外键，一个特殊的索引，只能是指定内容

creat table color(
nid int not null primary key,
name char(16) not null
)

create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)

添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称

外键foreign key, 一对多

两张表建立约束

MariaDB [test]> select * from tb4;
+-----+------+
| nid | num  |
+-----+------+
|   1 |  999 |
|   2 |  999 |
|   3 |  999 |
|   4 |  999 |
|   5 |  999 |
|   6 |  999 |
|   7 |  999 |
|   8 |  999 |
|   9 |  999 |
+-----+------+
9 rows in set (0.00 sec)

MariaDB [test]> delete from tb4;
Query OK, 9 rows affected (0.00 sec)

MariaDB [test]> select * from tb4;
Empty set (0.00 sec)

MariaDB [test]> insert into tb4(num) values(999);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tb4;    #delete清空自增表，自增列还记录上次自增列所在位置
+-----+------+
| nid | num  |
+-----+------+
|  10 |  999 |
+-----+------+
1 row in set (0.00 sec)

MariaDB [test]> truncate table tb4;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> select * from tb4;
Empty set (0.00 sec)

MariaDB [test]> insert into tb4(num) values(999);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from tb4;    #使用truncate清空自增表，自增列从最初位置开始，truncate速度快
+-----+------+
| nid | num  |
+-----+------+
|   1 |  999 |
+-----+------+
1 row in set (0.00 sec)

MariaDB [test]> create table userinfo(
    -> nid int not null auto_increment primary key,
    -> name varchar(10),
    -> age int,
    -> part_nid int)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table part(
    -> nid int not null auto_increment primary key,
    -> caption varchar(20))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> insert into part(caption) values('IT');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into part(caption) values('TI');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into part(caption) values('SA');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into part(caption) values('DEV');
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into userinfo(name,age,part_nid) values('h',19,1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into userinfo(name,age,part_nid) values('hh',19,1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> insert into userinfo(name,age,part_nid) values('hhh',19,100);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from userinfo;
+-----+------+------+----------+
| nid | name | age  | part_nid |
+-----+------+------+----------+
|   1 | h    |   19 |        1 |
|   2 | hh   |   19 |        1 |
|   3 | hhh  |   19 |      100 |
+-----+------+------+----------+
3 rows in set (0.01 sec)

MariaDB [test]> update userinfo set part_nid=2;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [test]> select * from userinfo;
+-----+------+------+----------+
| nid | name | age  | part_nid |
+-----+------+------+----------+
|   1 | h    |   19 |        2 |
|   2 | hh   |   19 |        2 |
|   3 | hhh  |   19 |        2 |
+-----+------+------+----------+
3 rows in set (0.00 sec)

MariaDB [test]> alter table userinfo add constraint fk_u_p foreign key userinfo(part_nid) references part(nid);    #fk_u_p随便起一个外键名字
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from part;
+-----+---------+
| nid | caption |
+-----+---------+
|   1 | IT      |
|   2 | TI      |
|   3 | SA      |
|   4 | DEV     |
+-----+---------+
4 rows in set (0.00 sec)

MariaDB [test]> insert into userinfo(name,age,part_nid) values('b',19,100);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`userinfo`, CONSTRAINT `fk_u_p` FOREIGN KEY (`part_nid`) REFERENCES `part` (`nid`))    #不能添加约束之外的信息