MySQL - Conditional Foreign Key Constraints?

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.

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.

Yes, EAV tables are just plain bad design most of the time. – HLGEM Jun 16 '10 at 17:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.