刚开始学数据库的时候，刚接触到的一个较为陌生的概念就是主键和外键。啥叫主键，从字面意思上来看感觉一脸懵。简单点说主键就类似每个人的身份证，代表一个唯一的某个人，而且主键不能为空，估计你也没见过哪个人没有身份证的。
那外键是啥，一个表中的某一个或多个字段是关联另一个表的主键，那我们把这一个或多个字段称为这个表的外键，外键可以是这个表的主键也可以不是。外键的作用是保证引用数据的完整性。
来看看书上是怎么定义主键的。主键：又称主码，是表中一列或多列的组合。下面来展示如何创建主键。
create table stu( id int PRIMARY KEY, name varchar(10), hobby varchar(20) );
Query OK, 0 rows affected (0.03 sec)
mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu | CREATE TABLE `stu` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`hobby` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)主键id显示的是NOT NULL，PRIMARY KEY (`id`)表明id是主键，上面是单字段主键。
mysql> create table stu1( id int , name varchar(10), hobby varchar(20),primary key(id,name) );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table stu1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu1 | CREATE TABLE `stu1` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
`hobby` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
上面是多字段主键。
mysql> create table stu2( id int primary key, stu_id int, name varchar(20),constraint fk_stu foreign key(stu_id) references stu(id) );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql>
mysql> show create table stu2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stu2 | CREATE TABLE `stu2` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_stu` (`stu_id`),
CONSTRAINT `fk_stu` FOREIGN KEY (`stu_id`) REFERENCES `stu` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
在表stu2上添加了fk_stu的外键约束，外键名称为stu_id，其依赖于表stu的主键id。下面来看外键保证引用数据的完整性 。
mysql> select * from stu2;
+----+--------+------+
| id | stu_id | name |
+----+--------+------+
| 1 | 1 | lisi |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from stu;
+----+----------+-------+
| id | name | hobby |
+----+----------+-------+
| 1 | zhangdan | daqiu |
+----+----------+-------+
1 row in set (0.00 sec)上面是2个表中的数据。表stu2的外键stu_id和stu的主键一一对应。现在再想表stu2中插入数据。
mysql> insert into stu2 values(1,2,"wangwu");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
插入记录报错了，因为表stu中没有id=2的记录。
mysql> insert into stu values(2,"wangwu","chifan");
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu;
+----+----------+--------+
| id | name | hobby |
+----+----------+--------+
| 1 | zhangdan | daqiu |
| 2 | wangwu | chifan |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> insert into stu2 values(2,2,"zhaoliu");
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu2;
+----+--------+---------+
| id | stu_id | name |
+----+--------+---------+
| 1 | 1 | lisi |
| 2 | 2 | zhaoliu |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql>
我们先向表stu表插入id=2的字段，再对stu2操作就成功了。主表先动，从表才能动。从表是含有外键约束的那个表，主表就是那个含有外键约束对应主键的那个表。再想想，如果试着删除某一行会发生啥。
mysql> delete * from stu2 where id=2;
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 '* from stu2 where id=2' at line 1
mysql> delete * from stu where id=2;
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 '* from stu where id=2' at line 1
mysql>
不管对stu还是stu2进行删除操作都会失败，因为2个表已经有了约束关系，谁也离不开谁了。
