mysql引用有两个_一个在MySQL中有两个引用的字段

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