多个但互斥的外键 - 这是要走的路吗？

Question

我有三个表：用户、公司和网站。 用户和公司都有网站，因此每个用户记录都有一个外键进入网站表。此外，每条公司记录都有一个进入 Websites 表的外键。

现在我想将 Websites 表中的外键包含回其各自的“父”记录中。我该怎么做？我是否应该在每个网站记录中有两个外键，其中之一始终为 NULL？或者还有别的路可走吗？

Answer 1

如果我们查看这里的模型，我们将看到以下内容：

1. 一位用户与一个网站相关
   • 一家公司只与一个网站相关
   • 一个网站只与一个用户或公司相关

第三种关系意味着存在一个“用户或公司”实体，其 PRIMARY KEY 应存储在某处。

要存储它，您需要创建一个表来存储网站所有者实体的主键。该表还可以存储用户和网站共有的属性。

由于它是一对一的关系，因此网站属性也可以存储在该表中。

用户和公司不共享的属性应存储在单独的表中。

要强制建立正确的关系，您需要将 website 的 PRIMARY KEY 与 owner type 组合在一起作为其一部分，并强制具有 CHECK 约束的子表中的正确类型：

CREATE TABLE website_owner (
    type INT NOT NULL,
    id INT NOT NULL,
    website_attributes,
    common_attributes,
    CHECK (type IN (1, 2)) -- 1 for user, 2 for company
    PRIMARY KEY (type, id)
)

CREATE TABLE user (
    type INT NOT NULL,
    id INT NOT NULL PRIMARY KEY,
    user_attributes,
    CHECK (type = 1),
    FOREIGN KEY (type, id) REFERENCES website_owner
)

CREATE TABLE company (
    type INT NOT NULL,
    id INT NOT NULL PRIMARY KEY,
    company_attributes,
    CHECK (type = 2),
    FOREIGN KEY (type, id) REFERENCES website_owner
)

Answer 2

您不需要父列，您可以通过在用户和公司表上进行简单的选择（或连接表）来查找父列。如果您想知道这是用户还是公司网站，我建议在您的网站表中使用布尔列。

Answer 3

为什么您需要从网站到用户/公司的外键？不重复数据的原则表明，最好扫描用户/公司表以查找匹配的网站 ID。如果您确实需要，您始终可以在网站表中存储一个标志，表示给定的网站记录是针对用户还是针对公司，然后扫描适当的表。

Answer 4

我对接受的答案（Quassnoi）的问题是，对象关系是错误的：公司不是网站所有者的子类型；公司不是网站所有者的子类型。在拥有网站之前我们就已经有了公司，而且我们可以拥有作为网站所有者的公司。另外，在我看来，网站所有权是网站与个人或公司之间的关系，即我们应该在架构中拥有一个（或两个）关系表。将个人网站所有权与公司网站所有权分开并仅在需要时将它们放在一起（例如通过VIEW）可能是一种可接受的方法：

CREATE TABLE People
(
 person_id CHAR(9) NOT NULL UNIQUE,  -- external identifier
 person_name VARCHAR(100) NOT NULL
);

CREATE TABLE Companies
(
 company_id CHAR(6) NOT NULL UNIQUE,  -- external identifier
 company_name VARCHAR(255) NOT NULL
);

CREATE TABLE Websites
(
 url CHAR(255) NOT NULL UNIQUE
);

CREATE TABLE PersonalWebsiteOwnership
(
 person_id CHAR(9) NOT NULL UNIQUE
    REFERENCES People ( person_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE TABLE CorporateWebsiteOwnership
(
 company_id CHAR(6) NOT NULL UNIQUE
    REFERENCES Companies( company_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE VIEW WebsiteOwnership AS
SELECT url, company_name AS website_owner_name
  FROM CorporateWebsiteOwnership
       NATURAL JOIN Companies
UNION
SELECT url, person_name AS website_owner_name
  FROM PersonalWebsiteOwnership
       NATURAL JOIN People;

上述问题是没有办法使用数据库约束来强制执行网站由个人或公司所有但不能同时由两者所有的规则。

如果我们可以假设 DBMS 强制执行检查约束（正如已接受的答案所做的那样），那么我们可以利用一个（人）人和一家公司都是法人的事实，并使用一个超类型表（LegalPersons)，但仍然保留关系表方法 (WebsiteOwnership)，这次使用 VIEW 将个人网站所有权与公司网站所有权分开，但这次使用强类型属性：

CREATE TABLE LegalPersons
(
 legal_person_id INT NOT NULL UNIQUE,  -- internal artificial identifier
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type IN ( 'Company', 'Person' ) ),
 UNIQUE ( legal_person_type, legal_person_id )
);

CREATE TABLE People
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type = 'Person' ),
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 person_id CHAR(9) NOT NULL UNIQUE,  -- external identifier
 person_name VARCHAR(100) NOT NULL
);

CREATE TABLE Companies
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
    CHECK ( legal_person_type = 'Company' ),
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 company_id CHAR(6) NOT NULL UNIQUE,  -- external identifier
 company_name VARCHAR(255) NOT NULL
);

CREATE TABLE WebsiteOwnership
(
 legal_person_id INT NOT NULL
 legal_person_type CHAR(7) NOT NULL
 UNIQUE ( legal_person_type, legal_person_id ),
 FOREIGN KEY ( legal_person_type, legal_person_id )
     REFERENCES LegalPersons ( legal_person_type, legal_person_id ),
 url CHAR(255) NOT NULL UNIQUE
    REFERENCES Websites ( url )
);

CREATE VIEW CorporateWebsiteOwnership AS 
SELECT url, company_name
  FROM WebsiteOwnership
       NATURAL JOIN Companies;

CREATE VIEW PersonalWebsiteOwnership AS
SELECT url, person_name
  FROM WebsiteOwnership
       NATURAL JOIN Persons;

我们需要的是“分布式外键”的新 DBMS 功能（“对于此表中的每一行，其中一个表中必须恰好有一行”）和“多重赋值”，以允许将数据添加到表中限制在单个 SQL 语句中。可悲的是，我们距离获得这样的功能还有很长的路要走！

Answer 5

首先，您真的需要这个双向链接吗？除非绝对需要，否则最好避免使用它。

据我了解，您想知道该网站是属于用户还是属于公司。您可以通过在 Website 表中添加一个简单的布尔字段 - [BelongsToUser] 来实现这一点。如果为 true，那么您会查找用户，如果为 false，您会查找一家公司。

Answer 6

有点晚了，但所有现有的答案似乎都有些达不到目标：

• 所有者到网站是 1:Many 关系
• 网站到所有者是 1:1 关系
• 用户和公司表不应在网站表中具有外键
• 网站数据（无论是否为用户和公司所共用）均不应出现在用户或公司表中
• 所有者的任何信息（无论是否共用）均不应出现在网站表中网站表
• MySQL 默默地忽略表上的 CHECK 约束（不强制执行引用完整性）
• DBMS 应该处理“关系”逻辑，而不是使用数据库的应用程序。

其中一些在 < a href="https://stackoverflow.com/a/40218845/7412956" title="Other Answer">来自 onedaywhen 的答案，但该答案仍然错过了让 MySQL 承担繁重工作并强制执行的机会参照完整性。

---

无论如何，一个网站在法律上只能有一个所有者。一个人或公司可以拥有任意数量的网站，也可以没有。数据库中从所有者到网站的链接在任何标准化级别都只能是 1:1。实际上，关系是1:Many，并且需要为恰好拥有多个网站的每个所有者拥有多个表条目。从数据库术语和现实情况来看，从网站到所有者的链接都是1:1。拥有从网站到所有者的链接可以更好地代表模型。通过网站表中的索引，对给定所有者执行 1:Many 查找变得相当高效。

SQL 中的 CHECK 属性将是一个很好的解决方案，如果 MySQL 没有碰巧默默地忽略它的话。

_{MySQL 文档 13.1.20创建表语法}

CHECK 子句会被解析，但会被所有存储引擎忽略。

MySQL 的功能确实提供了两种解决方案作为解决方案来实现 CHECK 行为并保持数据的引用完整性。带有存储过程的触发器就是其中之一，并且可以很好地适应各种约束。虽然通用性较差，但更容易实现的是使用 VIEW 和 WITH CHECK OPTION 子句，MySQL 将实现。

_{MySQL 文档 24.5 .4 视图WITH CHECK OPTION子句}

可以为可更新视图指定 WITH CHECK OPTION 子句，以防止插入到 select_statementWHERE 子句的行代码> 不正确。它还会阻止更新 WHERE 子句为 true 的行，但更新会导致其不为 true（换句话说，它会阻止可见行更新为不可见行）。

MySQLTUTORIAL 站点在其 SQL 简介中提供了这两个选项的一个很好的示例检查约束教程。 （您必须考虑拼写错误，但否则很好。）

---

在尝试解决类似的互斥外键拆分并开发解决方案时发现了这个问题，并根据答案生成了提示，似乎只适合在以下位置分享我的解决方案返回。

推荐的解决方案

为了将对现有架构和访问数据的应用程序的影响降至最低，请按原样保留 Users 和 Companies 表。重命名 Websites 表并将其替换为应用程序可以继续访问的名为 Websites 的 VIEW。除了处理所有权信息外，所有对 Websites 的旧查询应该仍然有效。因此：

设置

-- Keep the `Users` table about "users"
CREATE TABLE `Users` (
    `id` INT SERIAL PRIMARY KEY,
    `name` VARCHAR(180),
    -- user_attributes
);

-- Keep the `Companies` table about "companies"
CREATE TABLE `Companies` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(180),
    -- company_attributes
);

-- Attach ownership information about the website to the website's record in the `Websites` table, renamed to `WebsitesData`
CREATE TABLE `WebsitesData` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(255),
    `is_personal` BOOL,
    `owner_user` BIGINT UNSIGNED DEFAULT NULL,
    `owner_company` BIGINT UNSIGNED DEFAULT NULL,
    website_attributes,
    FOREIGN KEY `WebsiteOwner_User` (`owner_user`)
        REFERENCES `Users` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY `WebsiteOwner_Company` (`owner_company`)
        REFERENCES `Companies` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
);

-- Create a new `VIEW` with the original name of `Websites` as the gateway to the website records which can enforce the constraints you need
CREATE VIEW `Websites` AS
SELECT * FROM `WebsitesData` WHERE
    (`is_personal`=TRUE AND `owner_user` IS NOT NULL AND `owner_company` IS NULL) OR
    (`is_personal`=FALSE AND `owner_user` IS NULL AND `owner_company` IS NOT NULL)
WITH CHECK OPTION;

---

使用

-- Use the Websites VIEW for the INSERT, UPDATE, and SELECT operations as you normally would and leave the WebsitesData table in the background.
INSERT INTO `Websites` SET
    `is_personal`=TRUE,
    `owner_user`=$userID;
INSERT INTO `Websites` SET
    `is_personal`=FALSE,
    `owner_company`=$companyID;

-- Or, using different field lists based on the type of owner
INSERT INTO `Websites` (`is_personal`,`owner_user`, ...)
    VALUES (TRUE, $userID, ...);
INSERT INTO `Websites` (`is_personal`,`owner_company`, ...)
    VALUES (FALSE, $companyID, ...);

-- Or, using a common field list, and placing NULL in the proper place
INSERT INTO `Websites` (`is_personal`,`owner_user`,`owner_company`,...)
    VALUES (TRUE, $userID, NULL, ...);
INSERT INTO `Websites` (`is_personal`,`owner_user`,`owner_company`,...)
    VALUES (FALSE, NULL, $companyID, ...);

-- Change the company that owns a website
-- Will ERROR if the site was owned by a User.
UPDATE `Websites` SET `owner_company`=$new_companyID;

-- Force change the ownership from a User to a Company
UPDATE `Websites` SET
    `owner_company`=$new_companyID,
    `owner_user`=NULL,
    `is_personal`=FALSE;

-- Force change the ownership from a Company to a User
UPDATE `Websites` SET
    `owner_user`=$new_userID,
    `owner_company`=NULL,
    `is_personal`=TRUE;

-- Selecting the owner of a site without needing to know if it is personal or not
(SELECT `Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE AND `Websites`.`id`=$siteID)
UNION
(SELECT `Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE AND `Websites`.`id`=$siteID);

-- Selecting the sites owned by a User
SELECT `name` FROM `Websites`
    WHERE `is_personal`=TRUE AND `id`=$userID;
SELECT `Websites`.`name`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.$userID
    WHERE `is_personal`=TRUE AND `Users`.`name`="$user_name";

-- Selecting the sites owned by a Company
SELECT `name` FROM `Websites` WHERE `is_personal`=FALSE AND `id`=$companyID;
SELECT `Websites`.`name`
    FROM `Websites`
        JOIN `Comnpanies` ON `Websites`.`owner_company`=`Companies`.$userID
    WHERE `is_personal`=FALSE AND `Companies`.`name`="$company_name";

-- Listing all websites and their owners
(SELECT `Websites`.`name` AS `Website`,`Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE)
UNION ALL
(SELECT `Websites`.`name` AS `Website`,`Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE)
ORDER BY Website, Owner;

-- Listing all users or companies which own at least one website
(SELECT `Websites`.`name` AS `Website`,`Users`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Users` ON `Websites`.`owner_user`=`Users`.`id`
    WHERE `is_personal`=TRUE)
UNION DISTINCT
(SELECT `Websites`.`name` AS `Website`,`Companies`.`name` AS `Owner`
    FROM `Websites`
        JOIN `Companies` ON `Websites`.`owner_company`=`Companies`.`id`
    WHERE `is_personal`=FALSE)
GROUP BY `Owner` ORDER BY `Owner`;

---

标准化级别

作为标准化的技术说明，可以从 Websites 表中提取所有权信息，并创建一个新表来保存所有权数据，包括 is_normal 列。

CREATE TABLE `Websites` (
    `id` SERIAL PRIMARY KEY,
    `name` VARCHAR(255),
    `owner` BIGINT UNSIGNED DEFAULT NULL,
    website_attributes,
    FOREIGN KEY `Website_Owner` (`owner`)
        REFERENCES `WebOwners` (id`)
            ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE `WebOwnersData` (
    `id` SERIAL PRIMARY KEY,
    `is_personal` BOOL,
    `user` BIGINT UNSIGNED DEFAULT NULL,
    `company` BIGINT UNSIGNED DEFAULT NULL,
    FOREIGN KEY `WebOwners_User` (`user`)
        REFERENCES `Users` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
    FOREIGN KEY `WebOwners_Company` (`company`)
        REFERENCES `Companies` (`id`)
            ON DELETE RESTRICT ON UPDATE CASCADE,
);

CREATE VIEW `WebOwners` AS
SELECT * FROM WebsitesData WHERE
    (`is_personal`=TRUE AND `user` IS NOT NULL AND `company` IS NULL) OR
    (`is_personal`=FALSE AND `user` IS NULL AND `company` IS NOT NULL)
WITH CHECK OPTION;

然而，我相信，所创建的 VIEW 及其限制可以防止规范化旨在消除的任何异常情况，并增加情况下不需要的复杂性。无论如何，标准化过程始终是一种权衡。