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.
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;
...
By "schema name", I mean your database name. It's the same thing.
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);
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
I was still having some issues after finding this thread, but I have seemed to find a workflow that works for me.
If you are using MySQL Workbench, you will need to turn SAFE UPDATE off by doing the following 3 steps:
(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.
(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.
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
