MySql Script to temporarily change Foreign Key Constraints to Cascade then back

Question

I need to make changes to a complex DB with lots of “RESTRICTED” constraints.

Specifically, I need to renumber Id fields in a table UserInfo to which many other tables have Foreign Keys of “RESTICT” on Update and Delete.

I would like to create a script that:

1. Finds all the tables with constraints (Foreign Keys) to the “Id” field of “UserInfo” table
2. Loads the constraints (Foreign Keys) into a variable
3. Alter the dependent table(s) to “Drop” the Foreign Key
4. Alter the dependent table(s) to Add a the Foreign Key with “Cascade” on Delete and Update
5. … I will insert the update query here …
6. Alter the dependent table(s) to “Drop” the Foreign Key
7. Alter the dependent table(s) to Add back the original Foreign Key

I just can't figure out how to do step #2, loading the constraint into a variable.

I searched all through the information_schema DB and found the constraint names (i.e. userprefs_ibfk_1). But nowhere does it define what type of constraints they are (i.e. “RESTRICT on Delete”). Is there some secrect code to determining what type of constraint they are?

I might be able to use SHOW CREATE TABLE UserInfo but I cannot find a way to load the result into a variable (on which I can perform string functions).

I would greatly appreciate any help.

Answer 1

You were close, you need to check referential_constraints.update_rule or delete_rule:

SELECT constraint_name
FROM information_schema.referential_constraints
WHERE
    referenced_table_name = 'UserInfo'
    AND ( update_rule <> 'CASCADE' OR delete_rule <> 'CASCADE' )

You can wrap this query in a stored procedure, iterate over the results with a cursor, and build and execute prepared statements from it.