SQL Primary Key & Foreign Key

1. 定义

维基百科:主键(Primary Key)是数据表中每条记录唯一且完整的标识;外键(Foreign Key),又称外来键,是另一个数据表中的字段。

慕课解释:主键十分重要,每一张表都应有一个主键,且主键只能有一个,主键不能为空;外键用来表示表与表之间的关系,是关系数据库的核心,一般使用另一张表的主键作为外键。

2. 前言

本小节,我们将学习 SQL 中的主键外键

在前面的学习中,我们一直聚焦在 SQL 增删查改等操作。对于关系数据库,最核心的东西莫过于关系二字,在开发中,表 A 的主键一般会作为表 B 的外键,来表示表 A 与表 B 之间的关系。

注意: 在前面的小节中,新建数据库时均未指定主键,但实际上应该为每一张表都指定一个主键,后面的例子中将践行这条原则。

3. SQL Primary Key

SQL CREATE2 一节中,我们介绍了主键的基本用法。在实际的开发中,主键至关重要,所以一般还需加上其它约束,如 unsigned、auto_increment 等。

3.1 例1 创建用户表

请书写 SQL 语句,创建imooc_user表,共有 id,username 和 age 三个字段,其中 id 为主键,数据类型为 unsigned int 且自增,username 为 varchar 类型,age 表示年龄,为 int 类型。

分析

新建数据表使用 Create 指令,username 和 age 为基本类型字段,id 作为主键,字段类型为无符号整型,且自增,故该字段需要添加 auto_increment 约束。

语句

整理可得语句如下:

CREATE TABLE imooc_user
(
  id int unsigned PRIMARY KEY AUTO_INCREMENT,
  username varchar(20),
  age int
);

(MySQL数据库)创建成功后,imooc_user表信息如下:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | <null>  | auto_increment |
| username | varchar(20)      | YES  |     | <null>  |                |
| age      | int(11)          | YES  |     | <null>  |                |
+----------+------------------+------+-----+---------+----------------+

不同的数据库对于自增的支持是不同的,有些数据库甚至不支持自增主键,PostgreSQL 中使用 serial 类型来支持自增,如下:

CREATE TABLE imooc_user
(
  id serial PRIMARY KEY,
  username varchar(20),
  age int
);

4. SQL Foreign Key

外键是一类颇为特殊的字段,既可以像其它普通字段一样存储数据,更可以用来表示表与表之间的联系,这正是关系数据库的核心所在。

因为主键可以唯一标识一条记录,所以一张表的外键字段一般是另一张表的主键

外键是一种约束,其语法如下:

FOREIGN KEY ([col1]) REFERENCES [table_name]([col2])

其中col1表示当前表的外键字段名,table_name是另一张数据表名称,col2表示另一张表的字段名称。

4.1 例2 创建用户积分表

请书写 SQL 语句,创建imooc_user_score表,共有 id,user_score 和 user_id 三个字段,其中 id 字段为主键且自增,user_score 表示用户积分,数据类型为整形,user_id为外键指向 imooc_user 的主键 id。

分析

新建数据表使用 Create 指令,user_score 为基本类型字段,id 作为主键,user_id 为外键。

语句

整理可得语句如下:

CREATE TABLE imooc_user_score
(
  id int unsigned PRIMARY KEY AUTO_INCREMENT,
  user_score int,
  user_id int unsigned,
  FOREIGN KEY (user_id) REFERENCES imooc_user(id)
);

SQL 语句中,user_id 是 imooc_user_score 的字段,同时它也是外键,它的值来源于表 imooc_user 的 id 字段。

(MySQL)新建成功后,信息如下:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | <null>  | auto_increment |
| user_score | int(11)          | YES  |     | <null>  |                |
| user_id    | int(10) unsigned | YES  | MUL | <null>  |                |
+------------+------------------+------+-----+---------+----------------+

若使用 PostgreSQL,新建语句如下:

CREATE TABLE imooc_user_score
(
  id serial PRIMARY KEY,
  user_score int,
  user_id int,
  FOREIGN KEY (user_id) REFERENCES imooc_user(id)
);

5. 小结

  • PostgreSQL 的整型字段不支持 unsigned 无符号,且无自增约束,但有自增数据类型 serial。
  • 大部分场景中,主键一般都为自增字段,当然也有选择uuid作为主键的,但自增主键性能更加优异。
  • 外键是体现数据表关系的核心功能点,但主流的外键方式却都是弱外键