One field with two references in MySQL

Question

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

Answer 1

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

Answer 2

The approach you're trying is (a) not possible and (b) undesirable even if it was possible.

The best approach is to have a CustomerAddress table and a SupplierAddress table, each with a single FK to the matching base table; or if you must, a cross-reference table with appropriate constraints.

If your motivation for having a single Address table was code reuse, you can still do that ... think in terms of a template xxxAddress table design that can refer to any base xxx table. You can write non-database code that treats the base table name as a parameter and then could handle any number of xxxAddress tables as you add more base tables over time.

Or if your motivation for having a single Address table was to simplify reporting, you can always create a view or stored proc that returns a union of all such tables + an added field to indicate the base table for each address row.

Angelo I am revising this a bit based on your comment ---

Angelo, I ran your sample code in a local MySQL instance (not SQLFiddle) and observed an error.

I was surprised (you learn something every day) that MySQL did allow two foreign key constraints to be defined on the same field; however when you attempt to insert data, when trying to point the FK to the Customer table, I get an error saying a foreign key constraint fails referencing the Supplier table; and vice versa for the insert trying to point the FK to the Supplier table.

So my revised statement is (a) it is possible to create the hydra-headed FK in at least some DBMSs -- verified in MySQL, MS SQL Server and Oracle -- although (b) this only makes sense to use when the foreign key can refer to the same logical entity by ID across multiple tables (e.g. to ensure there is a corresponding record in all required tables, for example); and (c) if used to refer to multiple tables where the primary key is NOT the same logical entity, only works if by chance the same primary key value just happens to exist in all referenced tables, which is likely to lead to subtle, hard-to-find errors.

In other words, your example would work when attempting to insert a record referring to Customer ID=3 only if there was also a Supplier ID=3, which are really logically unrelated.

So my slightly revised answer to the OP is, what you're trying to do is not possible (or logical) when the foreign key is referring to different ENTITIES, as in the OP example of Customers and Suppliers.