Removing external relationships from all tables

Question

I have a database with several tables. Many tables have fields with foreign key constraints. I want to trim the tables and then re-populate them with new data, and I also want to remove the foreign keys, as some of the relationships have changed. basically, I want to compose FK constraints again. How to remove current FK constraints from all tables?

Answer 1

You can play with the information. Take a look at this page.

http://dev.mysql.com/doc/refman/5.0/en/key-column-usage-table.html

select concat('alter table ',table_name,' drop foreign key ',constraint_name,';') from information_schema.key_column_usage where constraint_schema = 'your_db' and referenced_table_name = 'table_name'; 

then run the generated result.

You can do something similar to truncate all tables.

 select concat('truncate ',table_name,';') from information_schema.tables where table_schema = 'your_db' and table_type = 'base table' 

this fragment will trim all tables in the specified database. Therefore use it with caution.

Answer 2

I assume that you have already found a solution, since this post is six months old, but I recently had to come up with a script to dump foreign key constraints into a specific table in MySQL so that this could help someone else in the same boat:

 # create a handy dandy stored procedure DELIMITER $$ CREATE PROCEDURE DropConstraints(refschema VARCHAR(64), reftable VARCHAR(64), refcolumn VARCHAR(64)) BEGIN WHILE EXISTS( SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE 1 AND REFERENCED_TABLE_SCHEMA = refschema AND REFERENCED_TABLE_NAME = reftable AND REFERENCED_COLUMN_NAME = refcolumn ) DO BEGIN SET @sqlstmt = ( SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE 1 AND REFERENCED_TABLE_SCHEMA = refschema AND REFERENCED_TABLE_NAME = reftable AND REFERENCED_COLUMN_NAME = refcolumn LIMIT 1 ); PREPARE stmt1 FROM @sqlstmt; EXECUTE stmt1; END; END WHILE; END$$ DELIMITER ; 

To start it just use:

 SET @schema = 'schema_name'; CALL DropConstraints(@schema, 'TableName', 'Referenced_Column_1'); CALL DropConstraints(@schema, 'TableName', 'Referenced_Column_2'); 

And if you do not want to support the procedure around:

 DROP PROCEDURE DropConstraints; 

Of course, if you want to remove all FK constraints in the table, you can remove the refcolumn parameter and the last line of each where clause.

Answer 3

  ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;