2

I have a large database with many tables, and "on-delete" action is set to cascade for all of them. Is it possible to change this option to "No action" in one go without having to open each and every table and each and every relation properties in Workbench ?

I presume it is not possible in Workbench interface, but I would bet it can be done programmatically using cursors that loop through all tables and change relations

Edgar Navasardyan
  • 4,261
  • 8
  • 58
  • 121
  • The tables `INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS` (look at column `delete_rule`) and `INFORMATION_SCHEMA.KEY_COLUMN_USAGE` (all the used and referenced columns) contain the data about the foreign key contraints. You can use this data to write a script that will generate the code for you. Someone already did an [SQL Script to drop all foreign keys](http://stackoverflow.com/questions/14546477/delete-all-foreign-keys-in-databasemysql#answer-32762058), the create part works similarly. You have to decide if writing a script will be faster for you (it's straight forward but needs some time). – Solarflare May 25 '16 at 12:23
  • 1
    That is a good comment @Solarflare ... why not make an answer out of it – Drew Jun 10 '16 at 00:34
  • @Edgar Navasardyan. Did you find a script? The comment points to postgres or sqlserver solutions. Do you have something for Mysql? – Inês Gomes Mar 30 '20 at 09:37

1 Answers1

-1

Is necessary to drop the constraint and add it with ON DELETE CASCADE.

You can have the list of all tables with the SQL to:

1-drop all

SELECT concat('ALTER TABLE ' , TABLE_NAME,' DROP FOREIGN KEY ' ,CONSTRAINT_NAME ,';' )
FROM  information_schema.REFERENTIAL_CONSTRAINTS  
WHERE  constraint_schema = '<database>';

2- add all

SELECT concat('ALTER TABLE ' , TABLE_NAME,' ADD FOREIGN KEY (', column_name ,') REFERENCES ', REFERENCED_TABLE_NAME ,' (', REFERENCED_COLUMN_NAME,') ON DELETE CASCADE;' )
FROM  information_schema.key_column_usage where 
constraint_name <>'PRIMARY' 
AND TABLE_SCHEMA = '<database>';

You can copy the result sql of the queries above and execute.

NOTE: Don't execute the first 'Drop all' before you have stored the information to create again given by the 2nd query (in a text file for example).

Inês Gomes
  • 4,313
  • 1
  • 24
  • 32