MYSQL怎么用命令删除某个表的全部关联外键约束？

Question

本帖最后由 ljm10 于 2010-09-28 11:04:56 编辑

Answer 1

不知道是不是这样:
set foreign_key_checks=0;
backup 表数据;
drop table tbname;
create table tbname;
load data 数据;
这样可以一下子全部删除完;要不就 alter table tbname drop foreign key  foreign_key_name

Answer 2

alter table tbname drop foreign key  foreign_key_name一般是用这个语句，一个一个外键进行删除。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

Answer 3

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol

Answer 4

toad for mysql，就把存储过程，触发器，视图，索引等都归类了，操作起来就很方便。

Answer 5

现在问题是不知道是表里的有什么外键约束，有些外键之前给建错了，现在想在先把所有外键先清除，于是自己写了个存储过程。然而执行存储过程时却出现错误Error Code : 1025，Error on rename of '.\db\test1' to '.\db\#sql2-bb8-8' (errno: 152)。而且错误的表是不固定的（有时是test1，有时是test2，有时是test3）请问这是什么问题？其存储过程如下：DELIMITER $$DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$CREATE DEFINER=`root`@`%` PROCEDURE `Proc_app_DelRef`(
    IN p_tablename VARCHAR(50) -- 表名
    )
BEGIN      
DECLARE done INT DEFAULT 0;
DECLARE v_constraintname VARCHAR(100);
        DECLARE cur CURSOR FOR 
SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
REPEAT      
            FETCH cur INTO v_constraintname; 
            IF v_constraintname IS NOT NULL THEN
                SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop foreign key ', v_constraintname);
                PREPARE stmt FROM @sqlcmd;
                EXECUTE stmt;  
                DEALLOCATE PREPARE stmt;   
            END IF;            
UNTIL done END REPEAT;
CLOSE cur;
    END$$DELIMITER ;

Answer 6

我外面还有个存储过程Proc_CheckDb是调用这个存储过程Proc_app_DelRef的
该存储过程内容如下：DELIMITER $$DROP PROCEDURE IF EXISTS `Proc_CheckDb`$$CREATE DEFINER=`root`@`%` PROCEDURE `Proc_CheckDb`(
)
BEGIN
    /*CALL Proc_app_DelRef('test1');
    CALL Proc_app_DelRef('test2');  
    CALL Proc_app_DelRef('test3');  
    ...
END$$DELIMITER ;
于是就报错Error Code : 1025，Error on rename of '.\db\test1' to '.\db\#sql2-bb8-8' (errno: 152)。

Answer 7

加上这句调一下，看看你的语句是什么？DELIMITER $$DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$CREATE DEFINER=`root`@`%` PROCEDURE `Proc_app_DelRef`(
    IN p_tablename VARCHAR(50) -- 表名
    )
BEGIN      
    DECLARE done INT DEFAULT 0;
    DECLARE v_constraintname VARCHAR(100);
        DECLARE cur CURSOR FOR 
    SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur;
    REPEAT         
            FETCH cur INTO v_constraintname; 
            IF v_constraintname IS NOT NULL THEN
                SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop foreign key ', v_constraintname);
                select @sqlcmd; --- debug
                PREPARE stmt FROM @sqlcmd;
                EXECUTE stmt;  
                DEALLOCATE PREPARE stmt;   
            END IF;            
    UNTIL done END REPEAT;
    CLOSE cur;
    END$$DELIMITER ;

Answer 8

alter table test1 drop foreign key FK_Reference_14

Answer 9

直接打开information_schema.REFERENTIAL_CONSTRAINTS 
REFERENCED_TABLE_NAME： 主表
TABLE_NAME：子表
CONSTRAINT_NAME：名字

Answer 10

alter table test1 drop foreign key FK_Reference_14这句直接执行是否有问题？

Answer 11

改为如下再试一下。应该是你执行的速度过快了。DELIMITER $$DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$CREATE DEFINER=`root`@`%` PROCEDURE `Proc_app_DelRef`(
    IN p_tablename VARCHAR(50) -- 表名
    )
BEGIN      
    DECLARE done INT DEFAULT 0;
    DECLARE v_constraintname VARCHAR(100);
        DECLARE cur CURSOR FOR 
    SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur;
    REPEAT         
            FETCH cur INTO v_constraintname; 
            IF v_constraintname IS NOT NULL THEN
                SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop foreign key ', v_constraintname);
                PREPARE stmt FROM @sqlcmd;
                EXECUTE stmt;  
                DEALLOCATE PREPARE stmt;   
                SLEEP(10000) ; ---- changed by ACMAIN
            END IF;            
            
    UNTIL done END REPEAT;
    CLOSE cur;
    END$$DELIMITER ;

Answer 12

哦，写错了，应该是 
select SLEEP(10000) ; 

Answer 13

 也不会死掉啊。
你的这些个表有多大？ drop foreign key 所花的时间是多少？

Answer 14

数据库还在死啊，就删除一个关联而已，就drop foreign key最多0.02秒

Answer 15

改了一下你代码，测试了没有问题。
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `Proc_app_DelRef`$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE  PROCEDURE `Proc_app_DelRef`(
    ->     IN p_tablename VARCHAR(50) -- 表名
    ->     )
    -> BEGIN
    ->     DECLARE done INT DEFAULT 0;
    ->     DECLARE v_constraintname VARCHAR(100);
    ->         DECLARE cur CURSOR FOR
    ->     SELECT constraint_name FROM information_schema.REFERENTIAL_CONSTRAINT
S
    ->     WHERE CONSTRAINT_schema=DATABASE() AND table_name=p_tablename;
    ->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    ->     OPEN cur;
    ->     REPEAT
    ->             FETCH cur INTO v_constraintname;
    ->             IF NOT done THEN
    ->                 SET @sqlcmd = CONCAT('alter table ',p_tablename,' drop fo
reign key ', v_constraintname);
    ->                 select @sqlcmd;
    ->                 PREPARE stmt FROM @sqlcmd;
    ->                 EXECUTE stmt;
    ->                 DEALLOCATE PREPARE stmt;
    ->             END IF;
    ->
    ->     UNTIL done END REPEAT;
    ->     CLOSE cur;
    ->     END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> call Proc_app_DelRef('tx');
Query OK, 0 rows affected (0.02 sec)mysql> drop table tx;
Query OK, 0 rows affected (0.02 sec)mysql> create table tx (
    ->  id int primary key,
    ->  c1 int,
    ->  c2 int,
    ->  c3 int,
    ->  CONSTRAINT c1 FOREIGN KEY f1 (c1) REFERENCES t1(id),
    ->  CONSTRAINT c2 FOREIGN KEY f2 (c2) REFERENCES t2(id),
    ->  CONSTRAINT c3 FOREIGN KEY f3 (c3) REFERENCES t3(id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.06 sec)mysql> call Proc_app_DelRef('tx');
+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c1 |
+------------------------------------+
1 row in set (0.00 sec)+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c2 |
+------------------------------------+
1 row in set (0.12 sec)+------------------------------------+
| @sqlcmd                            |
+------------------------------------+
| alter table tx drop foreign key c3 |
+------------------------------------+
1 row in set (0.51 sec)Query OK, 0 rows affected (0.56 sec)mysql>

Answer 16

我的是mysql5.2，难道是版本的问题？听说这个版本有这个问题的BUG
http://cavonchen.javaeye.com/blog/626522

Answer 17

我改了你的代码。注意修改的部分。IF NOT done THEN