Given a MariaDB table
customer, which other tables have a foreign key constraint on, I’m querying those table names with the following statement:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'customer';
Now, I’d like to delete the foreign keys in all tables from the above query but I don’t know how to do that. The result shall be something like the following, where
constraint_name are variables representing the result of the above query.
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
You can format the necessary SQL statements using that query:
SELECT CONCAT( 'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ', 'DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;' ) AS _sql FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'customer';
That will produce a set of strings. Execute each one as a new SQL statement.
Note I did not test this, so if I made any typos I’ll leave them to you to fix. The above example should be enough for you to get started.
Answered By – Bill Karwin
Answer Checked By – Mary Flores (BugsFixing Volunteer)