Re: Droppping a Foreign Key produces inconsistent results
OK, I see why this happens.
When the fk is added using ALTER TABLE, an entry is made in INFORMATION_SCHEMA.KEY_COLUMN_USAGE.
If the referencing column is not already indexed, MySQL indexes it and adds an entry in INFORMATION_SCHEMA.STATISTICS.
When the fk is dropped using ALTER TABLE, the entry in INFORMATION_SCHEMA.KEY_COLUMN_USAGE is deleted.
But the entry in INFORMATION_SCHEMA.STATISTICS remains (as the index is not removed).
This appears correct behaviour, because one would not necessarily want the "underlying" index removed.
If an fk is added on a non-indexed (referencing) column and another is added on a second non-indexed referencing column in the same table, then a two-column pk is added on these two columns...the pk cannot be dropped until the fks are dropped. In other words, although MySQL can replace an index on a referencing column when a pk is added, it cannot re-create it when the pk is dropped (in a multiple -column pk).
Any other intersting points to note around this behaviour?
Duncan
Edited 1 time(s). Last edit at 01/08/2009 08:11AM by Duncan Jack.
Subject
Views
Written By
Posted
2349
January 08, 2009 06:23AM
Re: Droppping a Foreign Key produces inconsistent results
1574
January 08, 2009 07:57AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.