Re: Droppping a Foreign Key produces inconsistent results

[Duncan Jack]

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.