MySQL - 条件外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2002985/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Conditional Foreign Key Constraints
提问by grzes
I have following commentstable in my app:
comments我的应用程序中有下表：
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?
现在，有没有办法强制对此类数据进行 FOREIGN KEY 约束？
i.e. something like this in comments table:
即评论表中的类似内容：
FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
-- but only when model='blogpost'
回答by Bill Karwin
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.
您正在尝试进行称为Polymorphic Associations的设计。也就是说，外键可以引用几个相关表中的任何一个中的行。
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 Commentstable. This would violate several rules of relational database design.
但是外键约束必须正好引用一个表。您不能根据表的另一列中的值声明引用不同表的外键Comments。这将违反关系数据库设计的几条规则。
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 BlogPostsor UserPictures, you must insert a new row to Commentableto generate a new pseudokey id. Then you can use that generated id as you insert the content to the respective subtype table.
在向BlogPostsor插入一行之前UserPictures，您必须插入一个新行Commentable以生成新的伪键 id。然后，您可以在将内容插入到相应的子类型表时使用生成的 id。
Once you do all that, you can rely on referential integrity constraints.
完成所有这些操作后，您就可以依赖参照完整性约束了。
