Homing foreign key - mysql is not set to null

Question

Indices:

Keyname Type Unique Packed Column Cardinality Collation Null parent_id BTREE No No parent_id 1 A YES 

Table: (comments)

 Column Type Null Default Extra id int(11) No None AUTO_INCREMENT parent_id int(11) Yes NULL 

Type of communication:

 Column Foreign key constraint (INNODB) parent_id 'test_site'.'comments'.'id' ON DELETE CASCADE ON UPDATE NO ACTION 

Is it possible that parent_id was not set to NULL. I tried to set the default value to "0" and insert the value "0", but I get the following error.

Mistake:

 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`test_site`.`comments`, CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) 

Any help on this would be greatly appreciated, thanks.

Answer 1

Yes, it is possible, although you only need to bypass the foreign key constraint once to insert a dummy entry for the default value. Here is my workflow:

This creates a table:

 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) 

Now bypass the foreign key and insert the dummy entry.

 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) 

To make things neat and tidy, I reset auto_increment (this is optional):

 root@localhost:playground > alter table comments auto_increment=0; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 

And now your foreign key constraint is working correctly, and your column is no longer valid and has a default value:

 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)