MySQL – Conditional Foreign Key Constraints

by

You’re attempting to do a design that is called Polymorphic Associations. That is, the foreign key may reference rows in any of several related tables.

But a foreign key constraint must reference exactly one table. You can’t declare a foreign key that references different tables depending on the value in another column of your Comments table. This would violate several rules of relational database design.

A better solution is to make a sort of “supertable” that is referenced by the comments. 

CREATE TABLE Commentable (
  id SERIAL PRIMARY KEY
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  foreign_id INT NOT NULL,
  ...
  FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);

Each of your content types would be considered a subtype of this supertable. This is analogous to the object-oriented concept of an interface.

CREATE TABLE BlogPosts (
  blogpost_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);

CREATE TABLE UserPictures (
  userpicture_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);

Before you can insert a row into BlogPosts or UserPictures, you must insert a new row to Commentable to generate a new pseudokey id. Then you can use that generated id as you insert the content to the respective subtype table.

Once you do all that, you can rely on referential integrity constraints.

More Related Contents:

  1. MySQL Cannot Add Foreign Key Constraint
  2. Required to join 2 tables with their FKs in a 3rd table
  3. Can a foreign key reference a non-unique index?
  4. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  5. How do I use on delete cascade in mysql?
  6. Entity-Attribute-Value Table Design
  7. How to create relationships in MySQL
  8. Remove Primary Key in MySQL
  9. Is it possible to reference one column as multiple foreign keys?
  10. Difference between two table structure
  11. Strings as Primary Keys in MYSQL Database [closed]
  12. Foreign key constraints: When to use ON UPDATE and ON DELETE
  13. When to use a left outer join?
  14. MySQL Relationships
  15. multiple query same table but in different columns mysql
  16. How can I make SQL case sensitive string comparison on MySQL?
  17. How to do a batch insert in MySQL
  18. Count the occurrences of DISTINCT values
  19. SQL: selecting rows where column value changed from previous row
  20. How to generate data in MySQL?
  21. How to skip certain database tables with mysqldump?
  22. MySQL error 1241: Operand should contain 1 column(s)
  23. MySQL – Supertype/Subtype design
  24. Mysql: Order by like?
  25. Getting first day of the week in MySql using Week No
  26. How to use django models with foreign keys in different DBs?
  27. Using DISTINCT and COUNT together in a MySQL Query
  28. List of Constraints from MySQL Database
  29. Execute INSERT if table is empty?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment