时间戳

首页 > 解决方案 > 外键可以引用多个表吗？

mysql - 外键可以引用多个表吗？

问题描述

我有这三个表：

CREATE TABLE userTypeA
(
  id VARCHAR (256) PRIMARY KEY,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

CREATE TABLE userTypeB
(
  id VARCHAR (256) PRIMARY KEY ,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

CREATE TABLE userTypeC
(
  id VARCHAR (256) PRIMARY KEY ,
  nickname VARCHAR (256) NOT NULL,
  password VARCHAR(70) NOT NULL DEFAULT '123456'
  --AND MORE OTHER ATTRIBUTTES DIFFERENT FROM OTHER USERTYPES
);

现在，我的想法是创建一个具有外键的表，该表可以引用三个表（userTypeA、userTypeB、userTypeC）之一的 id。在某些情况下，它将引用表 userTypeA，在其他情况下，它将引用表 userTypeB，而在其他情况下，它将引用 userTypeC。我试过这个，但似乎失败了：

CREATE TABLE account
(
    account_id VARCHAR (256),
    user_id VARCHAR (256),
    PRIMARY KEY (account_id),
    CONSTRAINT accounts_users_fk1 FOREIGN KEY (user_id) REFERENCES userTypeA (id),
    CONSTRAINT accounts_users_fk2 FOREIGN KEY (user_id) REFERENCES userTypeB (id),
    CONSTRAINT accounts_users_fk3 FOREIGN KEY (user_id) REFERENCES userTypeC (id),
  );

我一直在寻找一些例子，但没有一个看起来像我的。您是否知道能够使用单个外键引用三个表之一的任何方法，或者我应该创建不同的表并以不同的方式构造它们？

标签： mysqlsqlforeign-keyspolymorphic-associations

解决方案

You can create three separate columns, each with an accompanying foreign key, as in:

CREATE TABLE account (
  account_id VARCHAR (256),
  user_id_a VARCHAR (256),
  user_id_b VARCHAR (256),
  user_id_c VARCHAR (256),
  PRIMARY KEY (account_id),
  CONSTRAINT accounts_users_fk1 FOREIGN KEY (user_id_a) REFERENCES userTypeA (id),
  CONSTRAINT accounts_users_fk2 FOREIGN KEY (user_id_b) REFERENCES userTypeB (id),
  CONSTRAINT accounts_users_fk3 FOREIGN KEY (user_id_c) REFERENCES userTypeC (id),
  CONSTRAINT chk1 CHECK (
    user_id_a IS NOT NULL AND user_id_b IS NULL AND user_id_c IS NULL OR
    user_id_a IS NULL AND user_id_b IS NOT NULL AND user_id_c IS NULL OR
    user_id_a IS NULL AND user_id_b IS NULL AND user_id_c IS NOT NULL)
);

The CHECK constraint makes sure one and only one of the columns user_id_a, user_id_b, and user_id_c is not null at any given time.

Also, please note that CHECK constraints are validated in MySQL starting in version 8.0.16. Prior to that version CHECK constraints are parsed but not stored.

推荐阅读