是的,这是可能的,尽管您需要绕过一次外键约束来插入一个虚拟记录以获得默认值。以下是我的工作流程:
这是表格创建内容:
root@localhost:playground > create table comments(id int auto_increment primary key, parent_id int not null default 0, constraint fk_parent_id foreign key (parent_id) references comments(id) on delete cascade on update cascade)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
root@localhost:playground > show create table comments\G
*************************** 1. row ***************************
Table: comments
Create Table: CREATE TABLE `comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `fk_parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
现在绕过外键并插入虚拟记录。
root@localhost:playground > set session foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
root@localhost:playground > insert into comments (id) values (null); Query OK, 1 row affected (0.00 sec)
root@localhost:playground > set session foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)
root@localhost:playground > select * from comments;
+
| id | parent_id |
+
| 1 | 0 |
+
1 row in set (0.00 sec)
root@localhost:playground > update comments set id = 0 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:playground > select * from comments;
+
| id | parent_id |
+
| 0 | 0 |
+
1 row in set (0.00 sec)
为了使事情井然有序,我重置了auto_increment(这不是必要的):
root@localhost:playground > alter table comments auto_increment=0
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
从现在开始,您的外键约束已经正常工作,您的列不再可空,并且具有默认值:
root@localhost:playground > insert into comments (id) values (null);
Query OK, 1 row affected (0.00 sec)
root@localhost:playground > select * from comments;
+
| id | parent_id |
+
| 0 | 0 |
| 1 | 0 |
+
2 rows in set (0.00 sec)