在 SQL 中，当复合主键成为外键时应该如何被其它表引用

  当研究一个问题慢慢深入时，一个看起来简单的问题也暗藏玄机。在 SQL 中，主键成为外键这是一个很平常的问题，乍一看没啥值得注意的。但如果这个主键是一种复合主键，而另一个表又引用这个键作为它的复合主键，问题就会变得复杂。

  这里为了便于说明，简单抽象出这样一个情景。数据库中有很多用户（User），每个用户有他的好友分组（Folder），每个分组下面有该用户的好友（Contact）。下面看看应该如何建表。

• User 表建表示例代码如下：

  CREATE TABLE User (
      id VARCHAR(64) NOT NULL,
      name VARCHAR(64) NOT NULL,
      # ...为了简化说明，此表省略其它字段...
      PRIMARY KEY (id)
  );
  
  1
  2
  3
  4
  5
  6
  7

• Folder 表建表示例代码如下：

  CREATE TABLE Folder (
      id VARCHAR(64) NOT NULL,
      userId VARCHAR(64) NOT NULL,
      name VARCHAR(64) NOT NULL,
      # ...为了简化说明，此表省略其它字段...
      PRIMARY KEY (userId, id),
      # 因为上面的是复合主键，所以自动创建的是联合索引，而其它表的外键引用需要的是单个索引
      INDEX idIndex (id),
      FOREIGN KEY (userId) REFERENCES User (id)
  );
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• Contact 表建表示例代码如下：

  CREATE TABLE Contact (
      id VARCHAR(64) NOT NULL,
      # 表示此联系人属于谁的好友
      userId VARCHAR(64) NOT NULL,
      # 表示此联系人对应 User 中的 id
      linkedUserId VARCHAR(64) NOT NULL,
      folderId VARCHAR(64) NOT NULL,
      # ...为了简化说明，此表省略其它字段...
      PRIMARY KEY (userId, id),
      # 因为上面的是复合主键，所以自动创建的是联合索引，而其它表的外键引用需要的是单个索引
      INDEX idIndex (id),
      # 同一个用户，不能拥有两个相同 ID 的 Contact
      UNIQUE (userId, linkedUserId),
      # 当复合主键成为外键时，必须整个复合主键一起作为外键，不能只引用复合主键其中的某个属性
      FOREIGN KEY (userId) REFERENCES Folder (userId),
      FOREIGN KEY (folderId) REFERENCES Folder (id),
      FOREIGN KEY (linkedUserId) REFERENCES User (id)
  );
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

• 建表示意图如下：

  

  

  笔者已经将正确的建表在上述给出了，但问题其实不简单。注意看，表 Contact 引用了两个表的外键：表 User 和 表 Folder。而很奇妙的是，表 User、Folder 和 Contact 都有一个形同“userId”的字段，而且，它们的这个字段的意义是相同的。那么，表 Contact 中的 userId 究竟是引用表 User 中的 id 呢？还是引用表 Folder 中的 userId 呢？还是随便选一个都可以呢？

  答案是必须引用表 Folder 中的 userId，而不能引用表 User 中的 id。原因是，当复合主键成为外键时，必须整个复合主键一起作为外键，不能只引用复合主键其中的某个属性。

  注意看，表 Contact 的属性 folderId 已经引用了表 Folder 的属性 id 了，但表 Folder 的主键是 (id，userId)。在这个情形下，表 Contact 必须也同时引用表 Folder 关于这个复合主键的其它属性。而正好表 Contact 有一个属性 userId，所以它必须引用表 Folder 中的 userId。

  可是，表 Contact 不是也和表 User 有引用啊，为什么没有受到表 User 的约束呢？因为 User 的主键不是复合主键，而且表 Contact 已经通过属性 linkedUserId 引用了表 User 的主键 id，因此不需要强制引用 User 的其它属性。

  当然，这只是 SQL 上的逻辑，而 SQL 只是一套标准，各个数据库的服务提供商对 SQL 的支持和实现因人而异。在 MySQL 中，如果建表时错误地将上述表 Contact 中的 userId 引用了表 User 中的 id，MySQL 并不会抛出错误，因为 MySQL 关于这方面不太严谨。但如果使用的是 SQLite，SQLite 将会在建表时就抛出错误。但不管各厂商的支持如何，将应该保证数据库建表的逻辑是正确的。