I recently had a client who was using a table structure that utilized a self-referential structure using an ID field and Parent_ID fields. After upgrading to Innodb, his DBA was trying to utilize a foreign key constraint to perform cascading UPDATE and DELETE statements. After much heart-ache, they called me in to find out why it was not working. Unfortunately, I had to share with them the fact that MySQL does not support this type of use of constraints on self-referential tables.
This deviation from SQL standards results affects an ON UPDATE CASCADE or ON UPDATE SET NULL that recurses to update the same table it has previously updated during the cascade. Instead of cascading, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations in MySQL. According to the Innodb developers, this is to prevent infinite loops resulting from cascaded updates, although I would think this could be addressed in future versions. A self-referential ON DELETE SET NULL, on the other hand, is still possible, as is a self-referential ON DELETE CASCADE.
Filed in MySQL
|
Also tagged CASCADE, constraint, data, DBA, DELETE, Foreign, foreign key, Hierarchical, infinite, Innodb, key, MySQL, RESTRICT, sql, table, table structure, type, UPDATE
|
I recently was working with a 3rd party DB that was designed well and used lots of foreign keys to enforce referential integrity. Unfortunately, the application that used the DB structure was very poorly written and caused all sorts of problems when the foreign keys were applied. I had to quickly remove all the foreign keys from about 160 tables. I can never understand why developers will create foreign keys, however then not write their code properly to prevent errors, or at least attempt to catch those errors.
The code below will quickly create a script that removes all Foreign Keys from a database. Many folks will simply change the engine type to MyIsam and then back to InnoDB. This may work, but it is very slow if you already have a lot of data in your tables.
select concat(‘alter table ‘,table_schema,’.',table_name,’ DROP FOREIGN KEY ‘,constraint_name,’;') from information_schema.table_constraints
where constraint_type=’FOREIGN KEY’;
You can limit by schema by adding the line : AND table_schema rlike ‘Schema Name’
Remember, Foreign keys are only valid when using Innodb, MyISAM does not support foreign keys.
Filed in MySQL
|
Also tagged constraint, constraint name, data, DROP, Foreign, foreign key, Innodb, integrity, key, MyISAM, MySQL, night mare, schema name, table, table schema, type
|