Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove Foreign Key Relationships From All Tables

I have a database that has several tables. Many of the tables have fields with foreign key constraints. I want to truncate the tables and then repopulate them with new data, and I also want to take off the foreign keys, as some of the relationships have changed. basically, I want to build the FK constraints up from scratch again. How can I remove the current FK constraints from all tables?

like image 820
Bad Programmer Avatar asked Aug 13 '11 00:08

Bad Programmer


2 Answers

You can play with the information_schema. Take a look at this page

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

select concat('alter table ',table_name,' drop foreign key ',constraint_name,';') 
from information_schema.key_column_usage
where constraint_schema = 'your_db' and referenced_table_name = 'table_name';

then run the output generated.

You can do something similar in order to truncate all tables.

select concat('truncate ',table_name,';') 
from information_schema.tables
where table_schema = 'your_db' and table_type = 'base table'

this one will truncate all tables within the specified database. So use it with care.

like image 195
Nicola Cossu Avatar answered Sep 20 '22 16:09

Nicola Cossu


I"m guessing you already found a solution, since this post is six months old, but I recently had to come up with a script to drop foreign key constraints to a particular table in MySQL, so that may help anyone else in the same boat:

# create a handy dandy stored procedure
DELIMITER $$
CREATE PROCEDURE DropConstraints(refschema VARCHAR(64), reftable VARCHAR(64), refcolumn VARCHAR(64))
BEGIN
    WHILE EXISTS(
        SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE 1
        AND REFERENCED_TABLE_SCHEMA = refschema
        AND REFERENCED_TABLE_NAME = reftable
        AND REFERENCED_COLUMN_NAME = refcolumn
    ) DO
        BEGIN
            SET @sqlstmt = (
                SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME)
                FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                WHERE 1
                AND REFERENCED_TABLE_SCHEMA = refschema
                AND REFERENCED_TABLE_NAME = reftable
                AND REFERENCED_COLUMN_NAME = refcolumn
                LIMIT 1
            );
            PREPARE stmt1 FROM @sqlstmt;
            EXECUTE stmt1;
        END;
    END WHILE;
END$$
DELIMITER ;

To run it, simply use:

SET @schema = 'schema_name';
CALL DropConstraints(@schema, 'TableName', 'Referenced_Column_1');
CALL DropConstraints(@schema, 'TableName', 'Referenced_Column_2');

And if you don't want to keep the procedure around:

DROP PROCEDURE DropConstraints;

Of course if you want to drop all FK constraints to the table, you can remove the refcolumn parameter and the last line of each where clause.

like image 24
Sean the Bean Avatar answered Sep 19 '22 16:09

Sean the Bean