[SOLVED] Delete foreign keys with dynamic table and constraint names in MariaDB

Issue

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 table_name and constraint_name are variables representing the result of the above query.

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

Solution

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)