Delete foreign keys with dynamic table and constraint names in MariaDB

Question

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;

I think you need to do something with [DYNAMIC SQL](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) — Luuk, Mar 12 '22 at 13:09
and a cursor and a loop https://www.mysqltutorial.org/mysql-cursor/ — nbk, Mar 12 '22 at 13:33

score 1 · Accepted Answer · answered Mar 12 '22 at 17:12

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.

Delete foreign keys with dynamic table and constraint names in MariaDB

1 Answers1