目的

    这章目的就是要确认哪些使用了主键，却混淆了主键的本质而造成的一种反模式。

反模式（每个数据库表都需要一个伪主键）

    在表中，加入一个对于表的域模型无实际意义的列，叫做ID，用它来定位表中的某一条记录。

冗余键值
1
2
3
4
5
6
create table bug(
	id serial primary key,
	bug_id varchar(10) unique,
	description varchar(1000),
	...
)

    以上设计中，bug_id也用来定位到表中某一条数据，它和id有这相同的作用。

允许重复项
1
2
3
4
5
6
7
create table bug_product(
	id serial primary key,
	bug_id bigint unsigned not null,
	product_id bigint unsigned not null,
	foreign key(bug_id) references bug(bug_id),
	foreign key(product_id) references bug(product_id)
);

    上面设置使用id做为主键，但是它并不能约束bug_id,product_id的组合存在重复，那么你必须再添加unique约束，如下：

1
2
3
4
5
6
7
8
create table bug_product(
	id serial primary key,
	bug_id bigint unsigned not null,
	product_id bigint unsigned not null,
	unique key(bug_id, product_id),
	foreign key(bug_id) references bug(bug_id),
	foreign key(product_id) references bug(product_id)
);

    但是，当你在bug_id, product_id这两列上应用唯一约束，此时id这一列就会变成多余的。

意义不明的关键字

    单词code有很多意思，其一用于简化或加密消息，其二还有写代码的意思。而ID这个词，如此普通，完全无法表达更深层次的意思，特别是你在做两张表联结查询的时候

1
2
3
4
select b.id, a.id 
from bug b 
join account a on b.assigned_to = a.id
where b.status = 'OPEN';

    列名ID并不会是查询变得清晰，而使用bug_id或者account_id，事请就会变更加简单。

使用USING关键字

    你可能很熟悉联结查询

1
select * from bug as b join bus_product as bp on b.bug_id=bp.bug_id;

    SQL其实还支持另一种更见简洁的联结查询

1
select * from bug join bus_product USING(bug_id);

    然而，如果所有的表都是ID作为伪主键，那么只能使用ON做联结查询

使用组合键之难

如何识别反模式

当出现以下情况时，可能是反模式

  1. 我觉得这张表不需要主键；
  2. 我怎么能在多对多的表中存储重复的项；
  3. 我学过《数据库设计理论》，里面说我应该把数据移动到一张查询表中，然后通过ID查找。但是我不想这么做，因为每次我想要获得真是的数据，都不得不做一次连接查询。（这在数据库设计中是一个常见的误区，称为“正规化”，然而实际中对于伪主键并没有什么需要做的）

合理使用反模式

    使用伪主键，或者通过自动增长的整形的机制本身没有什么错误，但不是每张表都需要一个伪主键，更没有必要将每个伪主键都定义为ID。

解决方案

    主键是约束而非数据类型，你可以定义任意多的列为主键，只要其数据类型支持索引。你还可以将一个列定义为自增长的整形而不设定其为主键，这两者完全没有关系，别被既有的惯例限制住设计。

直接了当的描述设计

    为主键设置一个更有意义的名称，比如，bug表的主键应该叫bug_id。
    外键应该尽可能和所引用的列使用相同的名称，这通常意味这：一个主键的名称在整个数据库中的设计是唯一的。任意两张表都不应该使用相同的名称来定义主键，除非其中之一引用了另一个作为主键，但是有时也需要和其所引用的主键区分开，从而使他们之间引用关系表现的更加清晰。

1
2
3
4
5
create table bug(
  ....
  reported_by bigint unsigned not null,
  foreign key(reported_by) references account(account_id)
)
拥抱自然键和组合键

    如果你的表中包含一列能确认唯一、非空以及能够用来定位一条记录，就别仅仅因为传统而觉得有必要加上一个伪主键

总结

    以下都是我个人想法，看完本章我觉得我还是会继续使用id作为主键，另外就算有一列可能作为唯一标识，我也还是会加上id。
    举个栗子，假如现在有一张表，存在一列可以唯一的标识一条记录，我因此没有加上id列，产品上线之后，业务方变更需求说，当初那个可以唯一标识一条记录的列变成可以重复的了，那么你该怎么办，此时再加上id列，还来得及吗？