A Constraint That Only Allows One of Two Tables to Reference a Base Table

A constraint that only allows one of two tables to reference a base table

CHECK constraints with UDFs (which is Oded's answer) don't scale well and have poor concurrency. See these:

Scalar UDFs wrapped in CHECK constraints are very slow and may fail for multirow updates
Tony Rogerson

So:

create a new table, say TableA2XY
this has the PK of TableA and a char(1) column with a CHECK to allow ony X or Y. And a unique constraint on the PK of A too.
tableX and tableY have new char(1) column with a check to allow only X or Y respectively
tableX and tableY have their FK to TableA2XY on both columns

This is the superkey or subtype approach

all DRI based
no triggers
no udfs with table access in CHECK constraints.

In SQL, is it OK for two tables to refer to each other?

No, it's not OK. Circular references between tables are messy. See this (decade old) article: SQL By Design: The Circular Reference

Some DBMS can handle these, and with special care, but MySQL will have issues.

Option 1

As your design, to make one of the two FKs nullable. This allows you to solve the chicken-and-egg problem (which table should I first Insert into?).

There is a problem though with your code. It will allow a product to have a default picture where that picture will be referencing another product!

To disallow such an error, your FK constraint should be:

CONSTRAINT FK_products_1 
  FOREIGN KEY (id, default_picture_id) 
  REFERENCES products_pictures (product_id, id)
  ON DELETE RESTRICT                            --- the SET NULL options would 
  ON UPDATE RESTRICT                            --- lead to other issues

This will require a UNIQUE constraint/index in table products_pictures on (product_id, id) for the above FK to be defined and work properly.

Option 2

Another approach is to remove the Default_Picture_ID column form the product table and add an IsDefault BIT column in the picture table. The problem with this solution is how to allow only one picture per product to have that bit on and all others to have it off. In SQL-Server (and I think in Postgres) this can be done with a partial index:

CREATE UNIQUE INDEX is_DefaultPicture 
  ON products_pictures (Product_ID)
  WHERE IsDefault = 1 ;

But MySQL has no such feature.

Option 3

This approach, allows you to even have both FK columns defined as NOT NULL is to use deferrable constraints. This works in PostgreSQL and I think in Oracle. Check this question and the answer by @Erwin: Complex foreign key constraint in SQLAlchemy (the All key columns NOT NULL Part).

Constraints in MySQL cannot be deferrable.

Option 4

The approach (which I find cleanest) is to remove the Default_Picture_ID column and add another table. No circular path in the FK constraints and all FK columns will be NOT NULL with this solution:

product_default_picture
----------------------
product_id          NOT NULL
default_picture_id  NOT NULL
PRIMARY KEY (product_id)
FOREIGN KEY (product_id, default_picture_id)
  REFERENCES products_pictures (product_id, id)

This will also require a UNIQUE constraint/index in table products_pictures on (product_id, id) as in solution 1.

To summarize, with MySQL you have two options:

option 1 (a nullable FK column) with the correction above to enforce integrity correctly
option 4 (no nullable FK columns)

Possible to do a MySQL foreign key to one of two possible tables?

What you're describing is called Polymorphic Associations. That is, the "foreign key" column contains an id value that must exist in one of a set of target tables. Typically the target tables are related in some way, such as being instances of some common superclass of data. You'd also need another column along side the foreign key column, so that on each row, you can designate which target table is referenced.

CREATE TABLE popular_places (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  place_type VARCHAR(10) -- either 'states' or 'countries'
  -- foreign key is not possible
);

There's no way to model Polymorphic Associations using SQL constraints. A foreign key constraint always references one target table.

Polymorphic Associations are supported by frameworks such as Rails and Hibernate. But they explicitly say that you must disable SQL constraints to use this feature. Instead, the application or framework must do equivalent work to ensure that the reference is satisfied. That is, the value in the foreign key is present in one of the possible target tables.

Polymorphic Associations are weak with respect to enforcing database consistency. The data integrity depends on all clients accessing the database with the same referential integrity logic enforced, and also the enforcement must be bug-free.

Here are some alternative solutions that do take advantage of database-enforced referential integrity:

Create one extra table per target. For example popular_states and popular_countries, which reference states and countries respectively. Each of these "popular" tables also reference the user's profile.

CREATE TABLE popular_states (
  state_id INT NOT NULL,
  user_id  INT NOT NULL,
  PRIMARY KEY(state_id, user_id),
  FOREIGN KEY (state_id) REFERENCES states(state_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

CREATE TABLE popular_countries (
  country_id INT NOT NULL,
  user_id    INT NOT NULL,
  PRIMARY KEY(country_id, user_id),
  FOREIGN KEY (country_id) REFERENCES countries(country_id),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
);

This does mean that to get all of a user's popular favorite places you need to query both of these tables. But it means you can rely on the database to enforce consistency.

Create a places table as a supertable. As Abie mentions, a second alternative is that your popular places reference a table like places, which is a parent to both states and countries. That is, both states and countries also have a foreign key to places (you can even make this foreign key also be the primary key of states and countries).

CREATE TABLE popular_areas (
  user_id INT NOT NULL,
  place_id INT NOT NULL,
  PRIMARY KEY (user_id, place_id),
  FOREIGN KEY (place_id) REFERENCES places(place_id)
);

CREATE TABLE states (
  state_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (state_id) REFERENCES places(place_id)
);

CREATE TABLE countries (
  country_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

Use two columns. Instead of one column that may reference either of two target tables, use two columns. These two columns may be NULL; in fact only one of them should be non-NULL.

CREATE TABLE popular_areas (
  place_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  state_id INT,
  country_id INT,
  CONSTRAINT UNIQUE (user_id, state_id, country_id), -- UNIQUE permits NULLs
  CONSTRAINT CHECK (state_id IS NOT NULL OR country_id IS NOT NULL),
  FOREIGN KEY (state_id) REFERENCES places(place_id),
  FOREIGN KEY (country_id) REFERENCES places(place_id)
);

In terms of relational theory, Polymorphic Associations violates First Normal Form, because the popular_place_id is in effect a column with two meanings: it's either a state or a country. You wouldn't store a person's age and their phone_number in a single column, and for the same reason you shouldn't store both state_id and country_id in a single column. The fact that these two attributes have compatible data types is coincidental; they still signify different logical entities.

Polymorphic Associations also violates Third Normal Form, because the meaning of the column depends on the extra column which names the table to which the foreign key refers. In Third Normal Form, an attribute in a table must depend only on the primary key of that table.

Re comment from @SavasVedova:

I'm not sure I follow your description without seeing the table definitions or an example query, but it sounds like you simply have multiple Filters tables, each containing a foreign key that references a central Products table.

CREATE TABLE Products (
  product_id INT PRIMARY KEY
);

CREATE TABLE FiltersType1 (
  filter_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FiltersType2 (
  filter_id INT  PRIMARY KEY,
  product_id INT NOT NULL,
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

...and other filter tables...

Joining the products to a specific type of filter is easy if you know which type you want to join to:

SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)

If you want the filter type to be dynamic, you must write application code to construct the SQL query. SQL requires that the table be specified and fixed at the time you write the query. You can't make the joined table be chosen dynamically based on the values found in individual rows of Products.

The only other option is to join to all filter tables using outer joins. Those that have no matching product_id will just be returned as a single row of nulls. But you still have to hardcode all the joined tables, and if you add new filter tables, you have to update your code.

SELECT * FROM Products
LEFT OUTER JOIN FiltersType1 USING (product_id)
LEFT OUTER JOIN FiltersType2 USING (product_id)
LEFT OUTER JOIN FiltersType3 USING (product_id)
...

Another way to join to all filter tables is to do it serially:

SELECT * FROM Product
INNER JOIN FiltersType1 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType2 USING (product_id)
UNION ALL
SELECT * FROM Products
INNER JOIN FiltersType3 USING (product_id)
...

But this format still requires you to write references to all tables. There's no getting around that.

Foreign key to one of many tables?

No, a foreign key constraint always references exactly one parent table.

This question comes up frequently. Here are some of my past answers to it:

Why can you not have a foreign key in a polymorphic association?
Possible to do a MySQL foreign key to one of two possible tables?
Referencing foreign keys in the same column
In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
MySQL - Conditional Foreign Key Constraints
How to handle an “OR” relationship in an ERD (table) design?
MySQL: Two n:1 relations, but not both at once

For more on Polymorphic Associations, see my presentation Practical Object-Oriented Models in SQL or my book, SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

When two tables are very similar, when should they be combined?

Comments, PhotoComments, and EventComments are related in a pattern called "generalization specialization". This pattern is handled by simple inheritance in object oriented languages. It's a little more intricate to set up a schema of tables that will capture the same pattern.

But it's well understood. A quick google search on "generalization specialization relational modeling" will give you several good articles on the subject.

Foreign key constraints involving multiple tables

There is a very simple, bullet-proof solution. Works for Postgres 9.3 - when the original question was asked. Works for the current Postgres 13 - when the question in the bounty was added:

Would like information on if this is possible to achieve without database triggers

FOREIGN KEY constraints can span multiple columns. Just include the ID of table A in the FK constraint from table C to table B. This enforces that linked rows in B and C always point to the same row in A. Like:

CREATE TABLE a (
  a_id int PRIMARY KEY
);

CREATE TABLE b (
  b_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, UNIQUE (a_id, b_id)  -- redundant, but required for FK
);

CREATE TABLE c (
  c_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, b_id int
, CONSTRAINT fk_simple_and_safe_solution
  FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, b_id)  -- THIS !
);

Minimal sample data:

INSERT INTO a(a_id) VALUES
  (1)
, (2);

INSERT INTO b(b_id, a_id) VALUES
  (1, 1)
, (2, 2);

INSERT INTO c(c_id, a_id, b_id) VALUES
  (1, 1, NULL)  -- allowed
, (2, 2, 2);    -- allowed

Disallowed as requested:

INSERT INTO c(c_id, a_id, b_id) VALUES (3,2,1);

ERROR:  insert or update on table "c" violates foreign key constraint "fk_simple_and_safe_solution"
DETAIL:  Key (a_id, b_id)=(2, 1) is not present in table "b".

db<>fiddle here

The default MATCH SIMPLE behavior of FK constraints works like this (quoting the manual):

MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

So NULL values in c(b_id) are still allowed (as requested: "optional field"). The FK constraint is "disabled" for this special case.

We need the logically redundant UNIQUE constraint on b(a_id, b_id) to allow the FK reference to it. But by making it out to be on (a_id, b_id) instead of (b_id, a_id), it is also useful in its own right, providing a useful index on b(a_id) to support the other FK constraint, among other things. See:

Is a composite index also good for queries on the first field?

(An additional index on c(a_id) is typically useful accordingly.)

Can I use the same field to reference two tables, which both ultimately reference the same table?

-- Tree type TYP exists.
--
 tree_type {TYP}
        PK {TYP}

-- Tree sub-type SUB of tree type TYP exists.
--
 tree_sub {TYP, SUB}
       PK {TYP, SUB}

FK {TYP} REFERENCES tree_type {TYP}

-- Tree TRE is of tree type TYP, sub-type SUB.
--
 tree {TRE, TYP, SUB}
   PK {TRE}
   SK {TRE, TYP}

FK {TYP, SUB} REFERENCES tree_sub {TYP, SUB}

-- Forest FOR has only trees of type TYP.
--
forest {FOR, TYP}
    PK {FOR}
    SK {FOR, TYP}

FK {TYP} REFERENCES tree_type {TYP}

Each tree is part of at most one forest.

-- Tree TRE, of tree type TYP, is part of forest FOR.
--
forest_has_tree {TRE, TYP, FOR}
             PK {TRE}

FK1 {TRE, TYP} REFERENCES tree   {TRE, TYP}
FK2 {FOR, TYP} REFERENCES forest {FOR, TYP}

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

One field with two references in MySQL

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

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

A Constraint That Only Allows One of Two Tables to Reference a Base Table