Можно ли сразу удалить все ограничения внешнего ключа для таблицы в MySQL 5?

В mySQL 5 есть способ удалить все ограничения внешнего ключа для таблицы с помощью одного оператора SQL, не обращаясь к ним по имени?

Я пишу сценарий обновления БД, и, к сожалению, на некоторых сайтах были созданы ограничения с «неправильными» именами. Я пытаюсь не входить и не получать фактические имена ограничений из БД и не вставлять их обратно в операторы SQL.


mysql foreign-keys
person Alexei    schedule 24.08.2009    source источник

Ответы (5)


arrow_upward
6
arrow_downward

Вы, конечно, можете выбрать * таблицу во временную таблицу, удалить и воссоздать ее, а затем скопировать обратно.

person Zed    schedule 24.08.2009
comment
Для меня возникает ошибка типа Cannot delete or update a parent row: a foreign key constraint fails. Я пытался создать каждую строку, а затем только для одной строки с ошибкой, другие удалялись. - person Pramod; 05.12.2013
comment
Конечно вряд ли. В реальном мире таблицы часто слишком велики, чтобы это было возможно. Нет ли способа манипулировать индексами без воссоздания всей таблицы? (Да: см. отличное решение Дандальфа!) - person Lightness Races in Orbit; 08.01.2015

arrow_upward
12
arrow_downward

У меня есть решение, похожее на ответ Бинга, но он делает еще один шаг, чтобы быть автоматическим и многократно используемым в процедуре.

DROP PROCEDURE IF EXISTS dropForeignKeysFromTable;

delimiter ///
create procedure dropForeignKeysFromTable(IN param_table_schema varchar(255), IN param_table_name varchar(255))
begin
    declare done int default FALSE;
    declare dropCommand varchar(255);
    declare dropCur cursor for 
        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_name = param_table_name
            and table_schema = param_table_schema;

    declare continue handler for not found set done = true;

    open dropCur;

    read_loop: loop
        fetch dropCur into dropCommand;
        if done then
            leave read_loop;
        end if;

        set @sdropCommand = dropCommand;

        prepare dropClientUpdateKeyStmt from @sdropCommand;

        execute dropClientUpdateKeyStmt;

        deallocate prepare dropClientUpdateKeyStmt;
    end loop;

    close dropCur;
end///

delimiter ;

Поскольку подготовленный оператор может обрабатывать только один оператор за раз, процедура перебирает внешние ключи с помощью курсора и выполняет каждое удаление по одному.

Чтобы использовать процедуру для одной из ваших таблиц, просто используйте следующее, заменив table_schema и table_name вашими значениями:

call dropForeignKeysFromTable('table_schema', 'table_name');
person Dandalf    schedule 23.05.2013

arrow_upward
7
arrow_downward

Вот выбор, который создает полное удаление и создание операторов для всех отношений в таблице. Ни в коем случае не автоматический, но достаточно простой для копирования / вставки из ..

SELECT `DROP`,`CREATE` FROM (
SELECT
CONCAT("ALTER TABLE `", `K`.`TABLE_NAME`, "` DROP FOREIGN KEY `", `K`.`CONSTRAINT_NAME`, "`;") "DROP", 
CONCAT("ALTER TABLE `",
`K`.`TABLE_NAME`,
"` ADD CONSTRAINT ",
"`fk_",
`K`.`TABLE_NAME`,
"_",
`K`.`REFERENCED_TABLE_NAME`,
"1",
"` FOREIGN KEY (`",
`K`.`COLUMN_NAME`,
"`) REFERENCES ",
"`",
`K`.`REFERENCED_TABLE_SCHEMA`,
"`.`",
`K`.`REFERENCED_TABLE_NAME`,
"` (`",
`K`.`REFERENCED_COLUMN_NAME`,
"`) ON DELETE ",
`C`.`DELETE_RULE`,
" ON UPDATE ",
`C`.`UPDATE_RULE`,
";") "CREATE" 
FROM `information_schema`.`KEY_COLUMN_USAGE` `K`
LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `C` USING (`CONSTRAINT_NAME`)
WHERE `K`.`REFERENCED_TABLE_SCHEMA` = "your_db"
AND `K`.`REFERENCED_TABLE_NAME` = "your_table") AS DropCreateConstraints

Я использовал его, чтобы удалить все отношения, изменить типы данных на ключах, а затем восстановить отношения с именами внешних ключей а-ля MySQL Workbench. Может кому пригодится..

person Bing    schedule 07.12.2012

arrow_upward
6
arrow_downward

В свой сценарий вы всегда можете добавить SET FOREIGN_KEY_CHECKS=0, если вы просто хотите обойти ограничения.

Кроме того, я всегда удалял ограничения для каждого ограничения, используя:

ALTER TABLE <table_name> DROP FOREIGN KEY <key_name>;

Я не думаю, что вы можете сделать все это сразу, и я не смог найти ни одного примера, показывающего, что вы можете.

person northpole    schedule 24.08.2009

arrow_upward
6
arrow_downward

Вы можете использовать этот простой скрипт bash:

Бежать:

sh deleteForeignKeys.sh

и deleteForeignKeys.sh

#!/bin/bash

dbname="databasename"
table="tablename"

mysqlconn="mysql -u username -ppassword -h host"

tableschema=$($mysqlconn -N -e "SHOW CREATE TABLE $dbname.$table")

#Foreign Keys
fks=$(echo $tableschema | grep -oP '(?<=CONSTRAINT `).*?(?=` FOREIGN KEY)')

for fk in $fks; 
do
    # DROP FOREIGN KEY 
    $mysqlconn -e "ALTER TABLE $dbname.$table DROP FOREIGN KEY $fk";
done
person metdos    schedule 12.01.2012