【MySQL】FOREIGN KEY

1

1

• FOREIGN KEY reference PRIMARY KEY

CREATE TABLE `roottb` (
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE =InnoDB;


CREATE TABLE `subtb` (
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
INDEX (`rootid`),
FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE
) ENGINE =InnoDB;

插入数据，sub表的rootid使用root表中id的值

mysql> INSERT INTO `roottb` (`id`,`data`)
    -> VALUES ('1', 'test root line 1'),
    -> ('2', 'test root line 2'),
    -> ('3', 'test root line 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`)
    -> VALUES ('1', '1', 'test sub line 1 for root 1'),
    -> ('2', '1', 'test sub line 2 for root 1'),
    -> ('3', '1', 'test sub line 3 for root 1'),
    -> ('4', '2', 'test sub line 1 for root 2'),
    -> ('5', '2', 'test sub line 2 for root 2'),
    -> ('6', '2', 'test sub line 3 for root 2'),
    -> ('7', '3', 'test sub line 1 for root 3'),
    -> ('8', '3', 'test sub line 2 for root 3'),
    -> ('9', '3', 'test sub line 3 for root 3');
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from roottb;
+----+------------------+
| id | data             |
+----+------------------+
|  1 | test root line 1 |
|  2 | test root line 2 |
|  3 | test root line 3 |
+----+------------------+
3 rows in set (0.00 sec)
mysql> select * from subtb;
+----+--------+----------------------------+
| id | rootid | data                       |
+----+--------+----------------------------+
|  1 |      1 | test sub line 1 for root 1 |
|  2 |      1 | test sub line 2 for root 1 |
|  3 |      1 | test sub line 3 for root 1 |
|  4 |      2 | test sub line 1 for root 2 |
|  5 |      2 | test sub line 2 for root 2 |
|  6 |      2 | test sub line 3 for root 2 |
|  7 |      3 | test sub line 1 for root 3 |
|  8 |      3 | test sub line 2 for root 3 |
|  9 |      3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
9 rows in set (0.00 sec)

插入数据，sub表的rootid使用非root表中id的值

mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`)
    -> VALUES ('10', '4', 'test sub line 1 for root 4 not existed');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`subtb`, CONSTRAINT `subtb_ibfk_1` 
FOREIGN KEY (`rootid`) REFERENCES `roottb` (`id`) ON DELETE CASCADE)
mysql> 

• FOREIGN KEY reference UNIQUE KEY not primary

CREATE TABLE roottb3 (
id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
rootdata varchar(100) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY(rootdata)
)ENGINE=InnoDB;


CREATE TABLE subtb3 (
id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
subdata varchar(100) NOT NULL ,
PRIMARY KEY (id),
INDEX (subdata),
FOREIGN KEY (`subdata`) REFERENCES roottb3(`rootdata`) ON DELETE CASCADE on update CASCADE
)ENGINE =InnoDB;

INSERT INTO `roottb3` (`id`,`rootdata`)
VALUES ('1', 'aaa'),
('2', 'bbb'),
('3', 'ccc');

INSERT INTO `subtb3` (`id`,`subdata`)
VALUES ('1', 'aaa'),
       ('2', 'aaa'),
       ('3', 'aaa'),
       ('4', 'bbb'),
       ('5', 'bbb'),
       ('6', 'bbb'),
       ('7', 'ccc'),
       ('8', 'ccc'),
       ('9', 'ccc');

• FOREIGN KEY  -- ON DELETE CASCADE on update CASCADE

1.  on update CASCADE
   

   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  2 | bbb      |
   |  3 | ccc      |
   +----+----------+
   3 rows in set (0.00 sec)
   
   mysql> select * from subtb3;
   +----+---------+
   | id | subdata |
   +----+---------+
   |  1 | aaa     |
   |  2 | aaa     |
   |  3 | aaa     |
   |  4 | bbb     |
   |  5 | bbb     |
   |  6 | bbb     |
   |  7 | ccc     |
   |  8 | ccc     |
   |  9 | ccc     |
   +----+---------+
   9 rows in set (0.00 sec)
   
   mysql> 
   
   mysql> update roottb3 set rootdata='ddd' where id =3;
   Query OK, 0 rows affected (0.01 sec)
   Rows matched: 1  Changed: 0  Warnings: 0
   
   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  2 | bbb      |
   |  3 | ddd      |
   +----+----------+
   3 rows in set (0.00 sec)
   
   mysql> select * from subtb3;
   +----+---------+
   | id | subdata |
   +----+---------+
   |  1 | aaa     |
   |  2 | aaa     |
   |  3 | aaa     |
   |  4 | bbb     |
   |  5 | bbb     |
   |  6 | bbb     |
   |  7 | ddd     |
   |  8 | ddd     |
   |  9 | ddd     |
   +----+---------+
   9 rows in set (0.00 sec)

2. ON DELETE CASCADE

   mysql> delete from roottb3 where id=2;
   Query OK, 1 row affected (0.01 sec)
   
   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  3 | ddd      |
   +----+----------+
   2 rows in set (0.00 sec)
   
   mysql> select * from subtb3;
   +----+---------+
   | id | subdata |
   +----+---------+
   |  1 | aaa     |
   |  2 | aaa     |
   |  3 | aaa     |
   |  7 | ddd     |
   |  8 | ddd     |
   |  9 | ddd     |
   +----+---------+
   6 rows in set (0.00 sec)
   
   mysql> 

   3.　delete subtb data

   mysql> select * from subtb3;
   +----+---------+
   | id | subdata |
   +----+---------+
   |  1 | aaa     |
   |  2 | aaa     |
   |  3 | aaa     |
   |  7 | ddd     |
   |  8 | ddd     |
   |  9 | ddd     |
   +----+---------+
   6 rows in set (0.00 sec)
   
   mysql> delete from subtb3 where id=9;
   Query OK, 1 row affected (0.03 sec)
   
   mysql> 

   4. insert and delete the data of roottb that not used by subtable

   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  3 | ddd      |
   +----+----------+
   2 rows in set (0.00 sec)
   
   mysql> INSERT INTO `roottb3` (`id`,`data`) VALUES ('5', 'eeee'),('6', 'ffff');
   ERROR 1054 (42S22): Unknown column 'data' in 'field list'
   mysql> INSERT INTO `roottb3` (`id`,`rootdata`) VALUES ('5', 'eeee'),('6', 'ffff');
   Query OK, 2 rows affected (0.01 sec)
   Records: 2  Duplicates: 0  Warnings: 0
   
   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  3 | ddd      |
   |  5 | eeee     |
   |  6 | ffff     |
   +----+----------+
   4 rows in set (0.00 sec)
   
   mysql> delete from table roottb3 where rootdata = 'ffff';
   ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right
    syntax to use near 'table roottb3 where rootdata = 'ffff'' at line 1
   mysql> delete from  roottb3 where rootdata = 'ffff';
   Query OK, 1 row affected (0.01 sec)
   
   mysql> select * from roottb3;
   +----+----------+
   | id | rootdata |
   +----+----------+
   |  1 | aaa      |
   |  3 | ddd      |
   |  5 | eeee     |
   +----+----------+
   3 rows in set (0.00 sec)
   
   mysql> 

   　

• if without ON DELETE CASCADE on update CASCADE

       
 CREATE TABLE roottb4 (
id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
rootdata varchar(100) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY(rootdata)
)ENGINE=InnoDB;


CREATE TABLE subtb4 (
id INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
subdata varchar(100) NOT NULL ,
PRIMARY KEY (id),
INDEX (subdata),
FOREIGN KEY (`subdata`) REFERENCES roottb4(`rootdata`)
)ENGINE =InnoDB;

INSERT INTO `roottb4` (`id`,`rootdata`)
VALUES ('1', 'aaa'),
('2', 'bbb'),
('3', 'ccc');

INSERT INTO `subtb4` (`id`,`subdata`)
VALUES ('1', 'aaa'),
       ('2', 'aaa'),
       ('3', 'aaa'),
       ('4', 'bbb'),
       ('5', 'bbb'),
       ('6', 'bbb'),
       ('7', 'ccc'),
       ('8', 'ccc'),
       ('9', 'ccc');

result

mysql> 
mysql> select * from roottb4;
+----+----------+
| id | rootdata |
+----+----------+
|  1 | aaa      |
|  2 | bbb      |
|  3 | ccc      |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from subtb4;
+----+---------+
| id | subdata |
+----+---------+
|  1 | aaa     |
|  2 | aaa     |
|  3 | aaa     |
|  4 | bbb     |
|  5 | bbb     |
|  6 | bbb     |
|  7 | ccc     |
|  8 | ccc     |
|  9 | ccc     |
+----+---------+
9 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> update roottb4 set rootdata = 'ddd' where id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint 
fails (`test`.`subtb4`, CONSTRAINT `subtb4_ibfk_1` FOREIGN KEY (`subdata`) REFERENCES `roottb4` (`rootdata`))
mysql> 
mysql> delete from roottb4 where id =3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint
 fails (`test`.`subtb4`, CONSTRAINT `subtb4_ibfk_1` FOREIGN KEY (`subdata`) REFERENCES `roottb4` (`rootdata`))
mysql>