外键
1.外键是表的一个特殊字段,被参照的表是主表,外键所在字段的表为子表。设置外键的原则需要记住:
- 以来于数据库中已经存在的表的主键。
- 外键的作用是建立该表与其父表的关系关联。
- 父表中对记录做操作时,子表中与之对应的信息也应该有相应的改变
外键的作用
- 保证数据的一致性与完整性
- 可以实现一对一或者一对多的关系
外键注意事项
- 父表与子表必须使用相同的存储引擎,而且禁止使用临时表
- 数据表的存储引擎只能是InnoDB
- 外键列和参照列必须有相似的数据类型,其中数字的长度或者是否有符号必须相同:而且字符长度相同
- 外键列和参照列必须创建索引。如果外键列不存在索引,MYSQL会自动创建索引
外键的运用
- 这里我们举个例子
- 创建主表department包含id, depName
mysql> CREATE TABLE IF NOT EXISTS departmant(
-> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
-> depName VARCHAR(20) NOT NULL UNIQUE
-> )ENGINE = INNODB;
Query OK, 0 rows affected (0.39 sec)
mysql> INSERT departmant(depName) VALUES
-> ('教学部'),
-> ('市场部'),
-> ('运营部'),
-> ('督导部');
Query OK, 4 rows affected (0.04 sec)
mysql> SELECT*FROM departmant ORDER BY id ASC;
+----+-----------+
| id | depName |
+----+-----------+
| 1 | 教学部 |
| 2 | 市场部 |
| 3 | 运营部 |
| 4 | 督导部 |
+----+-----------+
4 rows in set (0.00 sec)
- 创建子表employee包含id, username,depId
- 内连接这两个表通过depid 与 d.id
- 删除督导部门,那就要先对督导部门下的人就要进行一些限制操作,这时候我们就要通过外键对副表进行限制操作,使得操作的顺序是对的。
这里写法是
FOREIGN KEY (a) REFERENCES main_table(b)
mysql> CREATE TABLE IF NOT EXISTS employee(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> depId TINYINT UNSIGNED,
-> FOREIGN KEY(depId) REFERENCES departmant(id)
-> )ENGINE = INNODB;
Query OK, 0 rows affected (0.48 sec)
mysql>
mysql> INSERT employee (username, depId) VALUES
-> ("king",1),
-> ("queen",2),
-> ("sunny",3),
-> ("windy",4),
-> ("rainy",1);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> DESC employee
-> ;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| depId | tinyint(3) unsigned | YES | MUL | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 1 | king | 1 |
| 2 | queen | 2 |
| 3 | sunny | 3 |
| 4 | windy | 4 |
| 5 | rainy | 1 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE employee
-> ;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`depId` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `depId` (`depId`),
CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `departmant` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
这里我们看到employee下多出了外键 “employee_ibfk_1”这是系统自动给出的索引。
- 这个时候我们就会发现,如果我们直接删除某一个部门例如
mysql> DELETE FROM departmant WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `departmant`
是删除不掉的。首先我们需要删除掉属于1部门下的员工,再删除部门,这时候就可以了
mysql> DELETE FROM employee WHERE depId = 1;
Query OK, 2 rows affected (0.47 sec)
mysql> DELETE FROM departmant WHERE id=1;
Query OK, 1 row affected (0.20 sec)
mysql> SELECT*FROM departmant ORDER BY id ASC;
+----+-----------+
| id | depName |
+----+-----------+
| 2 | 市场部 |
| 3 | 运营部 |
| 4 | 督导部 |
+----+-----------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM employee ORDER by id ASC;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 2 | queen | 2 |
| 3 | sunny | 3 |
| 4 | windy | 4 |
+----+----------+-------+
3 rows in set (0.00 sec)
这就是外键保证表格完整性与一致性的作用
添加与删除外键删除外键
- 使用ALTER TABLE tbl_name DROP FOREIGN KEY [索引名称]来删除外键
- 首先我们来新建一个员工表employee, 并且创建一个emp_fk_dep
mysql> CREATE TABLE IF NOT EXISTS employee(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> depId TINYINT UNSIGNED,
-> CONSTRAINT em_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
-> )ENGINE=INNODB;
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> CREATE TABLE IF NOT EXISTS employee(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> depId TINYINT UNSIGNED,
-> CONSTRAINT em_fk_dep FOREIGN KEY(depId) REFERENCES departmant(id)
-> )ENGINE=INNODB;
Query OK, 0 rows affected (0.64 sec)
mysql> SHOW CREATE TABLE employee;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`depId` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `em_fk_dep` (`depId`),
CONSTRAINT `em_fk_dep` FOREIGN KEY (`depId`) REFERENCES `departmant` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 这时候我们会发现有一个叫em_fk_dep的外键,这个时候如果向里插入
- 删除这个外键
mysql> ALTER TABLE employee DROP FOREIGN KEY em_fk_dep;
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`depId` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `em_fk_dep` (`depId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 这里在外键被删除的时候,索引是不会被删除的
- 使用ALTER TABLE 副表名称 ADD CONSTRAIN [索引名称] FOREIGN KEY(字段名称)REFERENCES 主表名称
- 这时候我们就会发现,垃圾数据又可以添加了
mysql> INSERT employee(username, depId) VALUES
-> ("king", 1),
-> ("queen",2),
-> ("lisa",3),
-> ("jennie",4),
-> ("rose",2);
Query OK, 5 rows affected (0.53 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT*FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 1 | king | 1 |
| 2 | queen | 2 |
| 3 | lisa | 3 |
| 4 | jennie | 4 |
| 5 | rose | 2 |
+----+----------+-------+
5 rows in set (0.00 sec)
mysql> SELECT*FROM departmant;
+----+-----------+
| id | depName |
+----+-----------+
| 2 | 市场部 |
| 4 | 督导部 |
| 3 | 运营部 |
+----+-----------+
3 rows in set (0.00 sec)
- 这时候我们会发现直接添加外键不成功,原因是一开始就没有departmant1, 所以我们要先把depId=1的数据删除掉
mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES departmant(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`#sql-147c_2`, CONSTRAINT `emp_fk_dep` FOREIGN KEY (`depId`) REFERENCES `departmant` (`id`))
mysql> DELETE FROM employee where depId=1;
Query OK, 1 row affected (0.53 sec)
mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES departmant(id);
Query OK, 4 rows affected (1.28 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employee;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`depId` tinyint(3) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `emp_fk_dep` (`depId`),
CONSTRAINT `emp_fk_dep` FOREIGN KEY (`depId`) REFERENCES `departmant` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
对父表进行删除和修改
在有外键的设置的情况下是默认不能更改父表的,当我们需要更改父表的时候则需要添加一下操作
- ON DELETE CASCADE ON UPDATE CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行
- 这里我们用employee 表与 department表为例子
mysql> CREATE TABLE IF NOT EXISTS department(
-> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
-> depName VARCHAR(20) NOT NULL UNIQUE
-> )engine = INNODB;
Query OK, 0 rows affected (0.52 sec)
mysql> INSERT department(depName) VALUES("EDUCATION"),
-> ("MARKETING"),
-> ("OPERATION"),
-> ("SUPERVISOR");
Query OK, 4 rows affected (0.13 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE IF NOT EXISTS employee(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> depId TINYINT UNSIGNED,
-> CONSTRAINT em_fk_dep FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
-> )ENGINE=INNODB;
Query OK, 0 rows affected (0.40 sec)
mysql> INSERT employee(username, depId) VALUES
-> ("king", 1),
-> ("queen",2),
-> ("lisa",3),
-> ("jennie",4),
-> ("rose",2);
- 这时候当我们删除编号为1的部门的时候,depId为1的员工也会相应的删除
mysql> DELETE FROM department where id=1;
Query OK, 1 row affected (0.48 sec)
mysql> SELECT*FROM department;
+----+------------+
| id | depName |
+----+------------+
| 2 | MARKETING |
| 3 | OPERATION |
| 4 | SUPERVISOR |
+----+------------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 2 | queen | 2 |
| 3 | lisa | 3 |
| 4 | jennie | 4 |
| 5 | rose | 2 |
+----+----------+-------+
4 rows in set (0.00 sec)
- 同样当我们更新一个数据的时候,子表的数据也会一起更新比如
mysql> UPDATE department SET id = id+10;
Query OK, 3 rows affected (0.56 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT*FROM department;
+----+------------+
| id | depName |
+----+------------+
| 12 | MARKETING |
| 13 | OPERATION |
| 14 | SUPERVISOR |
+----+------------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 2 | queen | 12 |
| 3 | lisa | 13 |
| 4 | jennie | 14 |
| 5 | rose | 12 |
+----+----------+-------+
4 rows in set (0.00 sec)
- ON DELETE SEL NULL ON UPDATE SET NULL: 从父表中删除或更行,并设置子表中的外键为NULL
- 若使用该选项贼必须保证子表列没有指定NOT NULL
mysql> CREATE TABLE IF NOT EXISTS employee(
-> id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> depId TINYINT UNSIGNED,
-> CONSTRAINT em_fk_dep FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
-> )ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> INSERT employee(username, depId) VALUES
-> ("king", 1),
-> ("queen",2),
-> ("lisa",3),
-> ("jennie",4),
-> ("rose",2);
Query OK, 5 rows affected (0.12 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> DELETE FROM department WHERE id=2;
Query OK, 1 row affected (0.19 sec)
mysql> SELECT*FROM department;
+----+------------+
| id | depName |
+----+------------+
| 1 | EDUCATION |
| 3 | OPERATION |
| 4 | SUPERVISOR |
+----+------------+
3 rows in set (0.00 sec)
mysql> SELECT*FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 1 | king | 1 |
| 2 | queen | NULL |
| 3 | lisa | 3 |
| 4 | jennie | 4 |
| 5 | rose | NULL |
+----+----------+-------+
5 rows in set (0.00 sec)