MySQL 外键约束

foreign key


mysql> create table t_class(
-> cno int primary key,
-> cname varchar(255));
Query OK, 0 rows affected (0.60 sec)

mysql> create table t_student (
-> sno int primary key,
-> sname varchar(255),
-> classno int,
-> foreign key(classno) references t_class(cno)
-> );
Query OK, 0 rows affected (0.51 sec)

mysql> desc t_student;
±--------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±-------------±-----±----±--------±------+
| sno | int | NO | PRI | NULL | |
| sname | varchar(255) | YES | | NULL | |
| classno | int | YES | MUL | NULL | |
±--------±-------------±-----±----±--------±------+
3 rows in set (0.06 sec)

mysql> desc t_class;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| cno | int | NO | PRI | NULL | |
| cname | varchar(255) | YES | | NULL | |
±------±-------------±-----±----±--------±------+
2 rows in set (0.01 sec)

mysql> insert into t_class values(101,‘cc’);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t_class values(102,‘dd’);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t_student values(1,‘zs’,‘101’);
Query OK, 1 row affected (0.12 sec)

mysql> insert into t_student values(2,‘zs2’,‘101’);
Query OK, 1 row affected (0.17 sec)

mysql> insert into t_student values(3,‘zs3’,‘102’);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t_student values(4,‘zs4’,‘102’);
Query OK, 1 row affected (0.09 sec)

mysql> insert into t_student values(5,‘zs5’,‘102’);
Query OK, 1 row affected (0.12 sec)

mysql> select * from t_class;
±----±------+
| cno | cname |
±----±------+
| 101 | cc |
| 102 | dd |
±----±------+
2 rows in set (0.00 sec)

mysql> select * from t_student;
±----±------±--------+
| sno | sname | classno |
±----±------±--------+
| 1 | zs | 101 |
| 2 | zs2 | 101 |
| 3 | zs3 | 102 |
| 4 | zs4 | 102 |
| 5 | zs5 | 102 |
±----±------±--------+
5 rows in set (0.00 sec)

mysql> insert into t_student values(6,‘zs5’,‘103’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (bjpowernode.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (classno) REFERENCES t_class (cno))

外键可以为null
外键引用 可以不是主键 但是必须有唯一性 unique；