mysql - MySQL 中一个字段有两个引用

Question

我有三个表:

CREATE TABLE Address (
  ResidentID CHAR(5) NOT NULL,
  Location varchar(255) NOT NULL,
  KEY ResidentID(ResidentID)
);

CREATE TABLE Customer (
  CustomerID CHAR(5) NOT NULL,
  ContactName varchar(40) NOT NULL,
  PRIMARY KEY (CustomerID)
);

CREATE TABLE Supplier (
  SupplierID CHAR(5) NOT NULL,
  SupplierName varchar(40) NOT NULL,
  PRIMARY KEY (SupplierID)
);

我想使用外键将 CustomerID 和 SupplyID 存储在 Address.ResidentID 字段中:

ALTER TABLE Address ADD CONSTRAINT fk_CustomerID1 FOREIGN KEY(ResidentID) REFERENCES Customer(CustomerID);
ALTER TABLE Address ADD CONSTRAINT fk_SupplierID1 FOREIGN KEY(ResidentID) REFERENCES Supplier(SupplierID);

但是第二个“ALTER TABLE”引发错误:关系已经存在

有什么建议吗？

数据示例:

CustomerID  ContactName
C0001       Den

SupplierID  ContactName
S0001       John

So Address table should contains:

ResidentID  Location
C0001       Alaska
S0001       Nevada

Answer 1

您需要引用客户/供应商的地址(如果他们只有一个)或两个不同的列。

你在这个 SQLFiddle 中看到的原因如果 ResidentID 引用两个表，则无法将所需列INSERT 到Address 表中。您只能插入与 Customer AND Supplier 内容匹配的行，但您想要一个 OR 连接，但无法以这种方式创建。

(注意:在我的解决方案中，我假设地址是可选的。正如 Tom 指出的 in the comments 这可能不是您想要或期望的。请确保将第一个解决方案中的 FK 列标记为 NOT NULL 如果您希望地址是强制性的，那么第二个地址会更复杂。那么您必须注意正确的插入顺序。)

或者:

CREATE TABLE Address (
  AddressID CHAR(5) NOT NULL,
  Location varchar(255) NOT NULL,
  PRIMARY KEY (AddressID)
);

CREATE TABLE Customer (
  CustomerID CHAR(5) NOT NULL,
  AddressID CHAR(5),
  ContactName varchar(40) NOT NULL,
  PRIMARY KEY (CustomerID)
);

CREATE TABLE Supplier (
  SupplierID CHAR(5) NOT NULL,
  AddressID CHAR(5),
  SupplierName varchar(40) NOT NULL,
  PRIMARY KEY (SupplierID)
);

ALTER TABLE Customer ADD CONSTRAINT fk_AddressID_Cust FOREIGN KEY(AddressID) REFERENCES Address(AddressID);
ALTER TABLE Supplier ADD CONSTRAINT fk_AddressID_Supp FOREIGN KEY(AddressID) REFERENCES Address(AddressID);

或

CREATE TABLE Address (
  CustomerID CHAR(5),
  SupplierID CHAR(5),
  Location varchar(255) NOT NULL,
  PRIMARY KEY (CustomerID, SupplierID)
);

CREATE TABLE Customer (
  CustomerID CHAR(5) NOT NULL,
  ContactName varchar(40) NOT NULL,
  PRIMARY KEY (CustomerID)
);

CREATE TABLE Supplier (
  SupplierID CHAR(5) NOT NULL,
  SupplierName varchar(40) NOT NULL,
  PRIMARY KEY (SupplierID)
);

ALTER TABLE Address ADD CONSTRAINT fk_CustomerID1 FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID);
ALTER TABLE Address ADD CONSTRAINT fk_SupplierID1 FOREIGN KEY(SupplierId) REFERENCES Supplier(SupplierID);