一个在MySQL中有两个引用的字段 [英] One field with two references in MySQL

问题描述

我有三个表：

$ pre $ CRE $ TABLE $地址
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供应商（
）供应商ID CHAR（5）NOT NULL，
供应商名称varchar（40）NOT NULL，
PRIMARY KEY（SupplierID）
） ;

我想通过使用外键将CustomerID和SupplierID存储在Address.ResidentID字段中：

  ALTER TABLE地址ADD CONSTRAINT fk_CustomerID1 FOREIGN KEY（ResidentID）REFERENCES Customer（CustomerID）; 
 ALTER TABLE地址ADD CONSTRAINT fk_SupplierID1 FOREIGN KEY（ResidentID）REFERENCES Supplier（SupplierID）; 
  

但第二个'ALTER TABLE'引发错误：关系已经存在 p>

有什么建议？

数据示例：

<$ p $ CustomerID ContactName
C0001 Den

SupplierID ContactName
S0001 John

所以地址表应该包含：

ResidentID Location
C0001 Alaska
S0001 Nevada

解决方案你需要引用客户/供应商（如果他们只有一个）或两个不同的列的地址

你看到的原因这个 SQLFiddle 你不能 INSERT ResidentID 引用了BOTH表，则将 Address 表中所需的列。您只能插入符合 Customer AND 供应商内容的行，但是您想要一个OR连接， （注意：在我的解决方案中，我假定地址是可选的，如 / tom> Tom 指出在评论中，可能不是你想要的，或者预期的。确保在第一个解决方案中将FK列标记为 NOT NULL 那么你必须介意正确的插入顺序。）

或者：

  CREATE TABLE地址（
 AddressID CHAR（5）NOT NULL，
位置varchar（255）NOT NULL，
 PRIMARY KEY（地址ID ）
）; 
 
 CREATE TABLE Customer（
 CustomerID CHAR（5）NOT NULL，
 AddressID CHAR（5），
 ContactName varchar（40）NOT NULL，
主键（CustomerID）
）; 
 
 CREATE TABLE供应商（
）供应商ID CHAR（5）NOT NULL，
 AddressID CHAR（5），
供应商名称varchar（40）NOT NULL，
主键（SupplierID）
）; 
 
 ALTER TABLE客户添加约束fk_AddressID_Cust FOREIGN KEY（AddressID）REFERENCES Address（AddressID）; 
 ALTER TABLE供应商ADD CONSTRAINT fk_AddressID_Supp FOREIGN KEY（AddressID）REFERENCES Address（AddressID）; 
  

或

  CustomerID CHAR（5），
供应商ID CHAR（5），
位置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供应商（
）供应商ID CHAR（5）NOT NULL，
供应商名称varchar（40）NOT NULL，
 PRIMARY KEY（SupplierID）
） ; 
 
 ALTER TABLE地址ADD CONSTRAINT fk_CustomerID1 FOREIGN KEY（CustomerID）REFERENCES Customer（CustomerID）; 
 ALTER TABLE地址ADD CONSTRAINT fk_SupplierID1 FOREIGN KEY（SupplierId）REFERENCES Supplier（SupplierID）; 
  

I have three tables:

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)
);

I want to store CustomerID and SupplierID in the Address.ResidentID field with using of foreign keys:

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);

But second 'ALTER TABLE' raises Error: relation already exists

Any suggestions?

Data example:

CustomerID  ContactName
C0001       Den

SupplierID  ContactName
S0001       John

So Address table should contains:

ResidentID  Location
C0001       Alaska
S0001       Nevada

解决方案

You need to either reference addresses from the Customer / Supplier (if they only have one) or two different columns.

The reason you see in this SQLFiddle You cannot INSERT the required columns into the Address table if the ResidentID references BOTH tables. You could only insert lines that would match the contents of Customer AND Supplier but you want an OR connection that you can't create that way.

(Note: In my solutions I assume addresses to be optional. As Tom pointed out in the comments that may not be what you wanted, or expected. Make sure to mark the FK Columns in the first solution as NOT NULL if you want addresses to be mandatory, its more complicated for the second one. You have to mind the correct insertion order then.)

Either:

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);

or

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);

这篇关于一个在MySQL中有两个引用的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！