测试表信息如下: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;是删除不了数据的,有没有办法在不删除外键的情况下删除数据?
/* 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;是删除不了数据的,有没有办法在不删除外键的情况下删除数据?
delete from 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: 0mysql> 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: 0mysql> 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>
+------+--------+
| 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>
+------+--------+
| 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: 0Query OK, 2 rows affected (0.03 sec)mysql>
-> ;
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | OFF |
| innodb_checksums | ON |
| unique_checks | ON |
+--------------------+-------+
3 rows in set (0.00 sec)