删除与自身表有外键约束关系的表中的数据

cqlxm 2009-10-07 09:22:25

测试表信息如下：



drop table if exists test;



/*==============================================================*/

/* Table: test                                                  */

/*==============================================================*/

create table test

(

   acct                 varchar(12) not null,

   p_acct               varchar(12),

   primary key (acct)

);



insert into test (acct,p_acct) values ('u1',null),('u2','u1');



alter table test add constraint FK_Reference_1 foreign key (p_acct)

      references test (acct) on delete restrict on update restrict;

这个时候，我用delete from test;是删除不了数据的，有没有办法在不删除外键的情况下删除数据？

...全文

132 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

xiaozhangnima 2009-10-08

打赏
举报

学习

nianzhang747 2009-10-07

打赏
举报

mysql> show variables like "%check%"

    -> ;

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| foreign_key_checks | OFF   |

| innodb_checksums   | ON    |

| unique_checks      | ON    |

+--------------------+-------+

3 rows in set (0.00 sec)

ACMAIN_CHM 2009-10-07

打赏
举报

mysql> select * from test;

+------+--------+

| acct | p_acct |

+------+--------+

| u1   | NULL   |

| u2   | u1     |

+------+--------+

2 rows in set (0.00 sec)



mysql> delete from test;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails (`csdn`.`test`, CONSTRAINT `FK_Reference_1` FOREIGN KEY (`p_acct`) REFE

RENCES `test` (`acct`))

mysql> update test set p_acct=null;delete from test;

Query OK, 1 row affected (0.06 sec)

Rows matched: 2  Changed: 1  Warnings: 0



Query OK, 2 rows affected (0.03 sec)



mysql>

ACMAIN_CHM 2009-10-07

打赏
举报

mysql> select * from test;

+------+--------+

| acct | p_acct |

+------+--------+

| u1   | NULL   |

| u2   | u1     |

+------+--------+

2 rows in set (0.00 sec)



mysql> delete from test;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails (`csdn`.`test`, CONSTRAINT `FK_Reference_1` FOREIGN KEY (`p_acct`) REFE

RENCES `test` (`acct`))

mysql> delete from test order by acct desc;

Query OK, 2 rows affected (0.06 sec)



mysql>

cqlxm 2009-10-07

打赏
举报

恩，感谢两位。
但是还有问题，我这里不能禁用约束检查，因为还有很多其他的约束。

百年树人 2009-10-07

打赏
举报

SET FOREIGN_KEY_CHECKS = 0; --禁用约束检查



delete from test;



SET FOREIGN_KEY_CHECKS = 1; --启用约束检查

ACMAIN_CHM 2009-10-07

打赏
举报

mysql> create table test

    -> (

    ->  acct    varchar(12) not null,

    ->  p_acct  varchar(12),

    ->    primary key (acct)

    -> );

Query OK, 0 rows affected (0.16 sec)



mysql>

mysql> insert into test (acct,p_acct) values ('u1',null),('u2','u1');

Query OK, 2 rows affected (0.11 sec)

Records: 2  Duplicates: 0  Warnings: 0



mysql> select * from test;

+------+--------+

| acct | p_acct |

+------+--------+

| u1   | NULL   |

| u2   | u1     |

+------+--------+

2 rows in set (0.00 sec)



mysql> alter table test add constraint FK_Reference_1 foreign key (p_acct)

    ->       references test (acct) on delete restrict on update restrict;

Query OK, 2 rows affected (0.20 sec)

Records: 2  Duplicates: 0  Warnings: 0



mysql> delete from test;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails (`csdn`.`test`, CONSTRAINT `FK_Reference_1` FOREIGN KEY (`p_acct`) REFE

RENCES `test` (`acct`))

mysql>



mysql> set @@foreign_key_checks=off;

Query OK, 0 rows affected (0.00 sec)



mysql> delete from test;

Query OK, 2 rows affected (0.06 sec)



mysql>