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

Question

Well here's my problem I have three tables; regions, countries, states. Countries can be inside of regions, states can be inside of regions. Regions are the top of the food chain.

Now I'm adding a popular_areas table with two columns; region_id and popular_place_id. Is it possible to make popular_place_id be a foreign key to either countries OR states. I'm probably going to have to add a popular_place_type column to determine whether the id is describing a country or state either way.

Answer 1

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.