外键必须引用一个父表。 这对于SQL语法和关系理论来说是基本的。
多态关联是指给定列可以引用两个或更多父表中的任何一个。在SQL中无法声明此约束。
多态关联设计违反了关系数据库设计规则。我不建议使用它。
有几种替代方案:
Exclusive Arcs: Create multiple foreign key columns, each referencing one parent. Enforce that exactly one of these foreign keys can be non-NULL.
Reverse the Relationship: Use three many-to-many tables, each references Comments and a respective parent.
Concrete Supertable: Instead of the implicit "commentable" superclass, create a real table that each of your parent tables references. Then link your Comments to that supertable. Pseudo-rails code would be something like the following (I'm not a Rails user, so treat this as a guideline, not literal code):
class Commentable < ActiveRecord::Base
has_many :comments
end
class Comment < ActiveRecord::Base
belongs_to :commentable
end
class Article < ActiveRecord::Base
belongs_to :commentable
end
class Photo < ActiveRecord::Base
belongs_to :commentable
end
class Event < ActiveRecord::Base
belongs_to :commentable
end
我在我的演讲Practical Object-Oriented Models in SQL和我的书SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming中也涉及多态关联。
关于您的评论:是的,我知道还有另一列记录了外键所指向的表的名称。但是,这种设计不受SQL外键支持。
例如,如果您插入一个名为“ Video”的Comment,则该父表的名称为“ Video”。但是没有名为“ Video”的表存在。应该中止插入并显示错误吗?违反了哪些约束条件?RDBMS如何知道此列应命名现有表格?它如何处理大小写不敏感的表格名称?
同样,如果删除Events表,但是在Comments中有指示Events为其父级的行,结果应该是什么?应该中止删除表操作吗?Comments中的行是否应成为孤儿行?它们是否更改为引用其他现有表格(例如Articles)?指向Events的id值在指向Articles时是否有意义?
所有这些困境都源于Polymorphic Associations依赖使用数据(即字符串值)来引用元数据(表格名称)。这在SQL中不受支持。数据和元数据是分开的。
我很难理解您的“Concrete Supertable”建议。
Define Commentable
as a real SQL table, not just an adjective in your Rails model definition. No other columns are necessary.
CREATE TABLE Commentable (
id INT AUTO_INCREMENT PRIMARY KEY
) TYPE=InnoDB;
Define the tables Articles
, Photos
, and Events
as "subclasses" of Commentable
, by making their primary key be also a foreign key referencing Commentable
.
CREATE TABLE Articles (
id INT PRIMARY KEY,
FOREIGN KEY (id) REFERENCES Commentable(id)
) TYPE=InnoDB;
Define the Comments
table with a foreign key to Commentable
.
CREATE TABLE Comments (
id INT PRIMARY KEY AUTO_INCREMENT,
commentable_id INT NOT NULL,
FOREIGN KEY (commentable_id) REFERENCES Commentable(id)
) TYPE=InnoDB;
When you want to create an Article
(for instance), you must create a new row in Commentable
too. So too for Photos
and Events
.
INSERT INTO Commentable (id) VALUES (DEFAULT);
INSERT INTO Articles (id, ...) VALUES ( LAST_INSERT_ID(), ... );
INSERT INTO Commentable (id) VALUES (DEFAULT);
INSERT INTO Photos (id, ...) VALUES ( LAST_INSERT_ID(), ... );
INSERT INTO Commentable (id) VALUES (DEFAULT);
INSERT INTO Events (id, ...) VALUES ( LAST_INSERT_ID(), ... );
When you want to create a Comment
, use a value that exists in Commentable
.
INSERT INTO Comments (id, commentable_id, ...)
VALUES (DEFAULT, 2, ...);
When you want to query comments of a given Photo
, do some joins:
SELECT * FROM Photos p JOIN Commentable t ON (p.id = t.id)
LEFT OUTER JOIN Comments c ON (t.id = c.commentable_id)
WHERE p.id = 2;
When you have only the id of a comment and you want to find what commentable resource it's a comment for. For this, you may find that it's helpful for the Commentable table to designate which resource it references.
SELECT commentable_id, commentable_type FROM Commentable t
JOIN Comments c ON (t.id = c.commentable_id)
WHERE c.id = 42;
接着,您需要运行第二个查询以从相应的资源表(照片、文章等)中获取数据,此前需要从commentable_type
中发现要连接的表。您无法在同一查询中执行此操作,因为SQL要求明确命名表;您不能在同一查询中加入由数据结果确定的表。
不可否认,其中一些步骤违反了Rails使用的约定。但是,Rails的约定在正确的关系数据库设计方面是错误的。
belongs_to
的foreign_key
选项。原帖在谈论本地数据库的“外键约束”。这让我困惑了一段时间。 - Joshua Pinter