一个表，其中包含两个一对一关系 SQL

Question

在我的数据库中，我有客户、经理和客户的表格。客户和经理都有帐户。此处存在客户-帐户和经理-帐户之间的两个一对一关系。我在客户表和经理表中都有帐户 ID 作为 FK。问题是，现在没有什么能阻止帐户同时绑定到经理和客户。我应该如何设计这种关系，或者我应该如何实现这件事。

Answer 1

使用帐户类型标记每个帐户。对于给定帐户，此列只能有一个值。

CREATE TABLE Accounts (
 account_id INT PRIMARY KEY,
 account_type TINYINT NOT NULL,
 UNIQUE KEY (account_id, account_type)
);

然后使每种类型的帐户将自身限制为一种特定的帐户类型。

CREATE TABLE Managers (
 account_id INT PRIMARY KEY,
 account_type TINYINT NOT NULL DEFAULT 1,
 FOREIGN KEY (account_id, account_type) 
   REFERENCES Accounts(account_id, account_type)
);
CREATE TABLE Customers (
 account_id INT PRIMARY KEY,
 account_type TINYINT NOT NULL DEFAULT 2,
 FOREIGN KEY (account_id, account_type) 
   REFERENCES Accounts(account_id, account_type)
);

这建立了默认值，但它不能防止错误的数据，例如在经理中用account_type 4 行。若要建立该约束，可以使用触发器、CHECK 约束或仅包含一个值的小表的外键。

---

重新发表您的评论：

其工作方式是，首先在"帐户"中插入一行，然后选择一个account_type：

INSERT INTO Accounts (account_id, account_type) VALUES (123, 1);

现在，此行只能由 account_type=1 的行引用。

如果您只在经理中插入行 account_type=1，而从不向具有该account_type的客户插入行，则帐号中的行只能由经理引用。

-- First example: works fine
INSERT INTO Managers (account_id, account_type) VALUES (123, 1);
-- Second example: wrong account_type for the Customers table
INSERT INTO Customers (account_id, account_type) VALUES (123, 1);
-- Third example: foreign key error, because Account 123 does not have account_type=2
INSERT INTO Customers (account_id, account_type) VALUES (123, 2);

客户和经理有自己的主键

这没关系。在我的示例中，我使用 account_id 作为主键，但这是可选的。如果您有不同的主键列，它仍然有效。例如：

CREATE TABLE Managers (
 id INT AUTO_INCREMENT PRIMARY KEY,
 account_id INT NOT NULL,
 account_type TINYINT NOT NULL DEFAULT 1,
 FOREIGN KEY (account_id, account_type) 
   REFERENCES Accounts(account_id, account_type)
);