In MySQL, Enter a row in the child table without having row in the parent table

Example of insert row in Child table without having a row in Parent table MySQL

  1. Create the table Parent P1 and Child C1.
mysql> create table p1(id integer primary key, name varchar(100));  Query OK, 0 rows affected (0.09 sec)    mysql> create table c1(cid integer primary key, pid integer, foreign key (pid) references p1(id));  Query OK, 0 rows affected (0.09 sec)

2. Insert data in the Parent and child table and child table throw error due to not presence of data in the parents table.

mysql> insert into p1 values (1,'a');  Query OK, 1 row affected (0.03 sec)    mysql> insert into p1 values (2,'b');  Query OK, 1 row affected (0.01 sec)    mysql> insert into c1 values (2,5);  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

3. Disable the foreign key check and enable it.

mysql> SET FOREIGN_KEY_CHECKS = 0;  Query OK, 0 rows affected (0.00 sec)    mysql> insert into c1 values (3,5);  Query OK, 1 row affected (0.02 sec)    mysql> SET FOREIGN_KEY_CHECKS = 1;  Query OK, 0 rows affected (0.00 sec)

4. After enabling the foreign key check, the insert query throw error again.

mysql> insert into c1 values (4,5);  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`c1`, CONSTRAINT `c1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `p1` (`id`))

5. Verify the data in both P1 and C1 tables.

mysql> select * from c1;  +-----+------+  | cid | pid  |  +-----+------+  |   3 |    5 |  +-----+------+  1 row in set (0.00 sec)    mysql> select * from p1;  +----+------+  | id | name |  +----+------+  |  1 | a    |  |  2 | b    |  +----+------+  2 rows in set (0.00 sec)
 This post is ad-supported
​