MySQL - Conditional Foreign Key Constraints

Question

I have following comments table in my app:

comments
--------
id           INT
foreign_id   INT
model        TEXT
comment_text TEXT
...

the idea of this table is to store comments for various parts of my app - it can store comments for blog post i.e.:

1|34|blogpost|lorem ipsum...

user picture:

2|12|picture|lorem ipsum...

and so on.

now, is there a way to force FOREIGN KEY constraint on such data?

i.e. something like this in comments table:

FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
-- but only when model='blogpost'

Daveta

Answer 1

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.

Theroid

I assume UserPictures contains a field user_id that references the User table. How do you handle a User delete in such a way that the delete will cascade to the Commentable table? I've asked this question here - #11497649 - and would be grateful if you could explain how you handle this hiccup I'm getting stuck on. – Mehta

@MattMcCormick, I don't answer questions on SO anymore, because the obnoxious moderators have made it unrewarding to participate. – Theroid

Oh ok. Thanks for your past participation. I read quite a few of your answers regarding single table inheritance and polymorphic associations and also watched the slides from your talk that you referenced in one of them. It helped me to better identify situations with the database that could lead to problem down the road. I've added your book to my reading list and will probably pick it up for my next software book to read. – Mehta

Polymorphic Associations This term help me a lot. – Granthem

@BillKarwin Glad to see that your comment above doesn't apply any more. – Magnesium

Is the goal here to allow for multiple comment records per BlogPost, or UserPicture? Otherwise, why not just make the foreign keys on UserPictures, and BlogPosts directly reference Comments(comment_id)? – Fionafionna

@BillKarwin, may u write your opinion about my question and answer here? – Practically

@RAM I don't use Entity Framework. But I think you should listen to the advice of Aluan Haddad. – Theroid

@BillKarwin, Thank u for your reply. Do you means I should define a separate column for each foreign key even when the Notification table needs to point to over 10 other table (over 10 foreign keys) and I'm sure in each record of Notification table just one of the foreign keys will have value and others will be null? – Practically

Fine, I'll post an answer. It's not appropriate to discuss it here. – Theroid

Answer 2

In MySQL 5.7 you can have a single polymorphic table AND enjoy something like a polymorphic foreign key!

The caveat is that technically you will need to implement it as multiple FKs on multiple columns (one per each entity that has comments), but the implementation can be limited to the DB side (i.e. you will not need to worry about these columns in your code).

The idea is to use MySQL's Generated Columns:

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  foreign_id INT,
  model TEXT,
  commented_text TEXT,
  generated_blogpost_id INT AS (IF(model = 'blogpost', foreign_id, NULL)) STORED,
  generated_picture_id INT AS (IF(model = 'picture', foreign_id, NULL)) STORED,
  PRIMARY KEY (id) ,
  FOREIGN KEY (`generated_blogpost_id`) REFERENCES blogpost(id) ON DELETE CASCADE,
  FOREIGN KEY (`generated_picture_id`) REFERENCES picture(id) ON DELETE CASCADE
)

You can ignore the generated_* columns; they will be populated automatically by MySQL as comments are added or modified, and the FKs defined for them will ensure data consistency as expected.

Obviously it would impact both the size requirements and performance, but for some (most?) systems it would be negligible, and a price worth paying for achieving data consistency with a simpler design.

Austine

Is this possible in Postgres? – Ledford