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

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.

Solution 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.

Solution 2:

This isn't the most elegant solution in the world, but you could use concrete table inheritance to make this work.

Conceptually you are proposing a notion of a class of "things that can be popular areas" from which your three types of places inherit. You could represent this as a table called, for example, places where each row has a one-to-one relationship with a row in regions, countries, or states. (Attributes that are shared between regions, countries, or states, if any, could be pushed into this places table.) Your popular_place_id would then be a foreign key reference to a row in the places table which would then lead you to a region, country, or state.

The solution you propose with a second column to describe the type of association happens to be how Rails handles polymorphic associations, but I'm not a fan of that in general. Bill explains in excellent detail why polymorphic associations are not your friends.

Solution 3:

Here is a correction to Bill Karwin's "supertable" approach, using a compound key ( place_type, place_id ) to resolve the perceived normal form violations:

CREATE TABLE places (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) NOT NULL
     CHECK ( place_type = 'state', 'country' ),
  UNIQUE ( place_type, place_id )
);

CREATE TABLE states (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'state' NOT NULL
     CHECK ( place_type = 'state' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to states go here
);

CREATE TABLE countries (
  place_id INT NOT NULL UNIQUE,
  place_type VARCHAR(10) DEFAULT 'country' NOT NULL
     CHECK ( place_type = 'country' ),
  FOREIGN KEY ( place_type, place_id ) 
     REFERENCES places ( place_type, place_id )
  -- attributes specific to country go here
);

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

What this design cannot ensure that for every row in places there exists a row in states or countries (but not both). This is a limitations of foreign keys in SQL. In a full SQL-92 Standards compliant DBMS you could define deferrable inter-table constraints that would allow you to achieve the same but it is clunky, involves transaction and such a DBMS has yet to make it to market.

Solution 4:

Relational Answer

Noting the mysql tag, which implies relational, because SQL is the data sub-language defined in Codd's Relational Model.

• The solution is simple and straight-forward, we had it before the RM, and we have had a Relational solution since 1981.
• The Relational solution provides both Referential Integrity (physical, at the SQL level) and Relational Integrity (logical).
• To comply with Open Architecture Standards (sanity), all Constraints; business rules; etc that govern the data, as well as all Transactions, should be deployed in the database, not the framework, not the app GUI, not the app middle-tier.  Note that it is a single recovery unit.

The polymorphic-associations tag is false, it was not requested by OP.  Forcing it into an OO/ORM mindset, and then proving a solution in that mindset, is outside the scope of the question.

• Further, it requires a framework, and code to enforce constraints; etc, outside the database, which is sub-standard.
• Further, it does not have the basic integrity, let alone Relational Integrity, of the Relational solution.
• Further, it violates 1NF and 3NF (as detailed in the Karvan Answer).
• Nulls are a Normalisation error, they should never be stored.
• A Nullable FOREIGN KEY is a gross Normalisation error.

Solution

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.

Make it Relational

Let's understand what that is in the Relational context.  It is a typical hierarchy of tables.

• Do not use ID fields.  Do not declare them as PRIMARY KEY, that will only confuse you, becasue it is not a Key, it does not provide row uniqueness as demanded in the Relational Model
• A Key must be made up from the data
• An ID field is not data.  It is always an additional field, and an additional index
• With ID fields, you might be able to implement Referential Integrity (physical, SQL), but you have no chance of implementing Relational Integrity (logical)
• For full discussion, including SQL code, refer to:
  Creating a Relational table with 2 different auto_increment, §1 & 2 only.

Base Tables

Notation

• All my data models are rendered in IDEF1X, the notation for Relational Data Modelling, which we have had since the early 1980’s, made the Standard for Relational Data Modelling, in 1993, last updated 2016.

• The IDEF1X Introduction is essential reading for those who are new to the Relational Model, or its modelling method.  Note that IDEF1X models are rich in detail and precision, showing all required details, whereas a home-grown model, being unaware of the imperatives of the Standard, have far less definition.  Which means, the notation needs to be fully understood.  

• ERD is not a Standard, it does not support the Relational Model, and it is completely inadequate for modelling.

• That the academics and "textbooks" teach and market anti-Relational as "relational" is criminal.

Subtype

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.

No problem at all.  The Relational Model is founded in Mathematics; Logic, it it entirely Logical.  An OR or XOR gate is a fundament of Logic.  In the Relational or SQL paradigm, it is called a Subtype cluster.

• Even in freeware "SQLs", which are not SQL compliant, it is done with full Referential Integrity

  • the notion that it can't be done, or that it requires the horrendous additional fields and indices marketed by the academics, is false.

• For full implementation details, including links to SQL code, refer to the Subtype document.

• For examples and discussion, refer to:
  How to Implement Referential Integrity in Subtypes

• For clarification of issues that confuse this Question, and thus the other Answers:
  Relational schema for a book graph

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.

Correct, you are thinking logically.  Here we need an XOR Gate, which requires a Discriminator.

Add Place Table

Relational Integrity

Whereas Referential Integrity is the physical feature provided in SQL, Relational Integrity, which is Logical, is on top of that (when modelling properly, the Logical precedes the physical).  

This is a great, simple example of Relational Integrity.  Note the second FOREIGN KEY in the Subtypes.

• PlaceCountry is constrained to a Country that is in the same Region as Place.Region

• PlaceState is constrained to a State that is in the same Region as Place.Region

• Note that this is possible only with Relational Keys (composite)

  • Relational Integrity is not possible in the primitive Record Filing Systems, which are characterised by ID fields as "keys", and heavily marketed by the academics and authors as "relational"
  • In such primitive files (they are not tables), PlaceCountry would allow any Country, it cannot be constrained to a Country that is in the same Region as Place.Region.