mysql --外键约束-foreign_key
时间：2021-07-01 10:21:17
帮助过：24人阅读
--外键约束；
----涉及到两个表：父表，子表；
----主表和副表。
--班级
create table classes(
id int primary key,
name varchar(
20)
);
--学生表
create table students(
id int primary key,
name varchar(
20),
class_id int,
foreign key(class_id)
references classes(id)
);
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra
|
+-------+-------------+------+-----+---------+-------+
| id
| int | NO
| PRI
| NULL | |
| name
| varchar(
20)
| YES
| | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows
in set (
0.03 sec)
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra
|
+----------+-------------+------+-----+---------+-------+
| id
| int | NO
| PRI
| NULL | |
| name
| varchar(
20)
| YES
| | NULL | |
| class_id
| int | YES
| MUL
| NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows
in set (
0.01 sec)
mysql>
mysql> mysql
> insert into classes
1,"yiban");
Query OK, 1 row affected (
0.01 sec)
mysql> insert into classes
values(
2,"erban");
Query OK, 1 row affected (
0.00 sec)
mysql> insert into classes
values(
3,"sanban");
Query OK, 1 row affected (
0.00 sec)
mysql> select * from classes;
+----+--------+
| id
| name
|
+----+--------+
| 1 | yiban
|
| 2 | erban
|
| 3 | sanban
|
+----+--------+
3 rows
in set (
0.00 sec)
mysql> insert into students
values(
1001,"zhangsan",
1);
Query OK, 1 row affected (
0.00 sec)
mysql> insert into students
values(
1002,"lisi",
2);
Query OK, 1 row affected (
0.00 sec)
mysql> insert into students
values(
1003,"lisi",
3);
Query OK, 1 row affected (
0.01 sec)
mysql> insert into students
values(
1004,"wangmazi",
4);
ERROR 1452 (
23000): Cannot
add or update a child row: a
foreign key constraint fails (`test`.`students`,
CONSTRAINT `students_ibfk_1`
FOREIGN KEY (`class_id`)
REFERENCES `classes` (`id`))
副表是需要参照主表
mysql> select * from students;
+------+----------+----------+
| id
| name
| class_id
|
+------+----------+----------+
| 1001 | zhangsan
| 1 |
| 1002 | lisi
| 2 |
| 1003 | lisi
| 3 |
+------+----------+----------+
3 rows
in set (
0.01 sec)
mysql>
---结论；
--1. 主表(父表)classes 中没有的数据值，在副表（子表）中，是不可以使用的；
--2. 主表中的记录被副表引用，是不可以被删除的。
mysql> delete from classes
where id
=3;
ERROR 1451 (
23000): Cannot
delete or update a parent row: a
foreign key constraint fails (`test`.`students`,
CONSTRAINT `students_ibfk_1`
FOREIGN KEY (`class_id`)
REFERENCES `classes` (`id`))
mysql>
mysql --外键约束-foreign_key
标签：mysql pre upd _id table where ann 约束 add
