3.逻辑型数据库设计反模式-需要id（主键id）

目录：

• 一.目标：建立主键规范
• 二.反模式:错误应用主键的情况
• 三.如何识别反模式
• 四.正确使用主键的情况

一.目标：建立主键规范[主键的意义何在]

主键是数据库确保数据行在整张表唯一性的保障。
它是定位到一条记录并且确保不会重复存储的逻辑机制。

主键也同时可以被外键引用来建立表与表之间的关系。

二.反模式:错误应用主键的情况

通用的设计情况：

• 主键的列名都叫做Id；
• 数据类型是32位或者64位的整数
• 主键的值是自动生成来确保唯一的。

在每张表中都存在一个叫做Id的列是如此地平常，甚至Id已经成为了主键的同义词。很多程序员在一开始学习SQL时就被灌输了错误的概念。认为每张表都要增加一列Id，这显然太过于随意。（应该随业务变化，不能这么死板）

2.1.冗余键值

两个字段都具有唯一标识（都符合作为主键的要求）

-- id 和 bug_id都具有标识作用
CREATE TABLE Bugs (
  id          SERIAL PRIMARY KEY,
  bug_id      VARCHAR(10) UNIQUE,
  description VARCHAR(1000),
  -- . . .
);

INSERT INTO Bugs (bug_id, description, ...)
  VALUES ('VIS-078', 'crashes on save', ...);

2.2.允许重复项

对于在交叉表中保证组合键的唯一性问题

设置了id为主键，不设置bug_id和produce_id的UNIQUE约束。->会出现重复数据。

-- START:typical
CREATE TABLE BugsProducts (
  id          SERIAL PRIMARY KEY,
  bug_id      BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1), (1234, 1), (1234, 1); -- duplicates are permitted

@From：《w3school文档-SQL UNIQUE 约束》

UNIQUE约束： 标识非主键的列值不重复，可以指定多个列。

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

设置UNIQUE约束，主键就显的冗余

-- START:unique
CREATE TABLE BugsProducts (
  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 Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
-- END:unique

2.3.意义不明确的关键字

就是起的字段名要有意义，不含糊。主键也一样。（业务中都在起别名，一般不会出现这种情况）

2.4.使用USING关键字

解释：在联表查询的时候使用JOIN ON来进行联表：

SELECT * FROM Bugs AS b JOIN BugsProducts AS bp ON (b.bug_id = bp.bug_id);

在这种有相同的关联id（bug_id）的情况下，可以使用using关键字来简化查询：

SELECT * FROM Bugs JOIN BugsProducts USING (bug_id);

如果是所有表都id字段作为伪主键，作为外键的列来连表就必须使用on来拼接。

SELECT * FROM Bugs AS b JOIN BugsProducts AS bp ON (b.id = bp.bug_id);

2.5.不想使用组合键的情况

一个表中定义多个主键的情况

番外：获取序列生成的最后一个值：

数据库类型	内置函数
mysql	LAST_INSET_ID()
oracle	SequenceName.CURRVAL()

三.如何识别反模式

最重要的一点：使用了过于普通的id作为了表的主键的列名。（使用更有意义的id）

• 反模式情况一：
  
  我觉得这张表不需要主键?
  

    每张表都必须要有一个主键,使用组合键，或者是更自然的列名来做主键。
  

• 反模式情况二：
  
  我怎么能在多对多的表中存储重复的项？
  

    在一个对多对关系的交叉表中需要声明一个主键约束。或者至少需要有一个针对那些被引用作为外键的唯一约束。
  

• 反模式情况 三：
  

    就是理论的范式化和实际业务的反范式化。（详见：page:40）
  

四.正确使用主键的情况-裁剪设计

4.1.直接了地描述设计

eg：如果是bug表就用bug_id，别整啥id来自找麻烦。

4.2.打破传统

要对每一列负责，起有意义的名称。至少在框架提取数据阿时候别名起的很要有点意义才好。

4.3.拥抱自然键和组合键

@From:《博客-转
选择主键：自然键还是代理键？》

键的术语：

• 键:键是唯一标识一个实体的一个或者多个数据属性。在物理数据库中，键可以由表的一个或者多个列组成，它们的值唯一标识关系表中的一行。
• 复合键:也叫组合键，由两个或者多个属性组成的键。
• 自然键: 由现实世界中已经存在的属性组成的键。例如，美国公民被分配了一个唯一（不保证一定正确，但实际上非常接近唯一）的社会保险号（SSN）。如果隐私法允许的话，SSN可能被用作Person实体的自然键（假设组织的人员范围仅限于美国）。
• 代理键:不具有业务含义的键。
• 候选键:在逻辑数据模型中的实体类型可能具有0或多个候选键，简称为唯一标识（注解：某些人不主张在逻辑数据模型中标识候选键，因此没有固定标准）。例如，假设我们只涉及美国公民，那么SSN是Person实体类型的一个候选键，同时姓名与电话号码的组合（假设组合是唯一的）是第二个可能的候选键。这两个键都被称作候选键是因为它们是物理数据模型中主键、次键或者非键的候选目标。
• 主键:实体类型的首选键。
• 备用键:也就是次键，是表中行的另一个唯一标识。
• 外键:在一个实体类型中表示另一个实体类型的主键或者次键的一个或多个属性。

重点：数据库的设计趋向于在整个项目的声明周期中不断地调整和优化 。

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

在合适的时候也可以使用联合主键，比如一条记录可以通过多个列的组合完全定位。就像上面提到的Contact表，那就通过那些列创建一个联合主键 。（拥抱组合键）

例子：

-- START:table
CREATE TABLE BugsProducts (
  bug_id      BIGINT UNSIGNED NOT NULL,
  product_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
-- END:table

-- START:insert
INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1), (1234, 2), (1234, 3);

INSERT INTO BugsProducts (bug_id, product_id)
  VALUES (1234, 1); -- error: duplicate entry
--END:insert

注意：一个引用组合键的外键同样需要是一个组合键。

（解释：这看上去像是很累赘，其依赖的列做了一个副本，这么做也有好处，它能简化原来需要一个联结查询才能获得的一个记录的相关属性。）