Delete all foreign keys in database(MySql)

问题:

I would like to rename a column in a table that is a foreign key to many tables. Apparently this is only possible if you delete the constraints, as I found out in this link.

I dont want to delete all the constratints manually is there a way to delete all the foreign key constraints in the database?

I have also tried SET FOREIGN_KEY_CHECKS=0; but I still cant rename the column.

回答1:

Executing the following query

select * from information_schema.key_column_usage

will show you all the constraints (with the column name, constraint type, table and schema) that exist in your database. You'll notice these columns:

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

Then, if you're planning to delete each constraint you have referencing your column, you should consider the REFERENCED_* columns and run something like:

DELETE FROM information_schema.key_column_usage 
WHERE 
    REFERENCED_TABLE_SCHEMA='myschema'
    AND
    REFERENCED_TABLE_NAME='mytable'
    AND
    REFERENCED_COLUMN_NAME='mycolumn'

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

回答2:

You can use this SQL to generate ALTER TABLES (!!YOUR_SCHEMA_HERE!! needs to be replaced by your schema):

SELECT concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';')
FROM information_schema.table_constraints
WHERE constraint_type='FOREIGN KEY'
AND table_schema='!!YOUR_SCHEMA_HERE!!';

It will generate SQL like this:

alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_codebook_item1;
alter table viewpoint_test.answer_code DROP FOREIGN KEY fk_answer_code_questionary_answer1;
alter table viewpoint_test.codebook DROP FOREIGN KEY codebook_ibfk_1;
...

回答3:

You Can Try using As Like of Following ..

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

Also you can try with Reference Key.As like .....

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

回答4:

The following query will build the correct syntax automatically. Make sure you put your real DB schema name in the WHERE condition. Just execute each line returned and all your FOREIGN KEYS will be gone.

I leave the reverse (adding them back) as an exercise for you.

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key ", CONSTRAINT_NAME,"; ") AS runMe
FROM information_schema.key_column_usage 
WHERE TABLE_SCHEMA='MY_SCHEMA_NAME' AND CONSTRAINT_NAME <> 'PRIMARY';

If you need it to be schema independent, you can write: TABLE_SCHEMA=DATABASE() to get the current active DB name

回答5:

I was still having some issues after finding this thread, but I have seemed to find a workflow that works for me.

Database Deletion

If you are using MySQL Workbench, you will need to turn SAFE UPDATE off by doing the following 3 steps:

1. MySql Workbench > Preferences > SQL EDITOR > (bottom of settings)
2. turn the SAFE UPDATE off
3. then log out of your connection and reconnect

Steps to start purge database and start new

1. Delete all data from tables

(This MySQL script will create a DELETE FROM script for each table in your database.)

SELECT concat('DELETE FROM ',table_schema,'.',table_name,';') FROM information_schema.table_constraints WHERE table_schema='!!TABLE_SCHEMA!!';

Copy the output of this and run it. You might need this line-by-line.

2. Delete all Foreign Keys from your tables

(This MySql script will create na ALTER TABLE _ DROP FOREIGN KEY script for each table in your database.)

SELECT concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') FROM information_schema.table_constraints WHERE constraint_type='FOREIGN KEY' AND table_schema='!!TABLE_SCHEMA!!';

Copy the output of this and run it. You might need this line-by-line.

After you've done this, you SHOULD be able to run your DROP DATABASE script.