Ruby-on-rails 为什么在多态关联中不能有外键？

Question

提问by eggdrop

Why can you not have a foreign key in a polymorphic association, such as the one represented below as a Rails model?

为什么在多态关联中不能有外键，例如下面表示为 Rails 模型的那个？

class Comment < ActiveRecord::Base
  belongs_to :commentable, :polymorphic => true
end

class Article < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

class Photo < ActiveRecord::Base
  has_many :comments, :as => :commentable
  #...
end

class Event < ActiveRecord::Base
  has_many :comments, :as => :commentable
end

Answer 1

回答by Bill Karwin

A foreign key must reference only one parent table. This is fundamental to both SQL syntax, and relational theory.

一个外键只能引用一个父表。这是 SQL 语法和关系理论的基础。

A Polymorphic Association is when a given column may reference either of two or more parent tables. There's no way you can declare that constraint in SQL.

多态关联是指给定的列可以引用两个或多个父表中的任何一个。您无法在 SQL 中声明该约束。

The Polymorphic Associations design breaks rules of relational database design. I don't recommend using it.

多态关联设计打破了关系数据库设计的规则。我不建议使用它。

There are several alternatives:

有几种选择：

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

独占弧：创建多个外键列，每个外键列引用一个父列。强制这些外键之一可以是非 NULL。
反转关系：使用三个多对多表，每个表都引用 Comments 和各自的父级。

具体的超级表：不是隐式的“可评论”超类，而是创建一个真实的表，每个父表都引用它。然后将您的评论链接到该超级表。伪 Rails 代码类似于以下内容（我不是 Rails 用户，因此将其视为指南，而不是文字代码）：

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

I also cover polymorphic associations in my presentation Practical Object-Oriented Models in SQL, and my book SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

我还在我的演示文稿SQL 中的实用面向对象模型和我的书SQL 反模式：避免数据库编程的陷阱中介绍了多态关联。

Re your comment: Yes, I do know that there's another column that notes the name of the table that the foreign key supposedly points to. This design is not supported by foreign keys in SQL.

回复您的评论：是的，我知道还有另一列记录了外键应该指向的表的名称。SQL 中的外键不支持这种设计。

What happens, for instance, if you insert a Comment and name "Video" as the name of the parent table for that Comment? No table named "Video" exists. Should the insert be aborted with an error? What constraint is being violated? How does the RDBMS know that this column is supposed to name an existing table? How does it handle case-insensitive table names?

例如，如果您插入一条评论并将“视频”命名为父表的名称，会发生什么情况Comment？不存在名为“视频”的表。插入是否应该因错误而中止？违反了什么约束？RDBMS 如何知道该列应该命名现有表？它如何处理不区分大小写的表名？

Likewise, if you drop the Eventstable, but you have rows in Commentsthat indicate Events as their parent, what should be the result? Should the drop table be aborted? Should rows in Commentsbe orphaned? Should they change to refer to another existing table such as Articles? Do the id values that used to point to Eventsmake any sense when pointing to Articles?

同样，如果您删除该Events表，但其中的行Comments指示 Events 作为其父项，结果应该是什么？应该中止删除表吗？行应该Comments是孤立的吗？他们是否应该更改以引用另一个现有表，例如Articles？过去指向的 id 值Events在指向时是否有意义Articles？

These dilemmas are all due to the fact that Polymorphic Associations depends on using data (i.e. a string value) to refer to metadata (a table name). This is not supported by SQL. Data and metadata are separate.

这些困境都是由于多态关联依赖于使用数据（即字符串值）来引用元数据（表名）这一事实。这不受 SQL 支持。数据和元数据是分开的。

I'm having a hard time wrapping my head around your "Concrete Supertable" proposal.

我很难围绕你的“混凝土超级表”提案。

Define Commentableas 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 Eventsas "subclasses" of Commentable, by making their primary key be also a foreign key referencing Commentable.

CREATE TABLE Articles (
  id INT PRIMARY KEY, -- not auto-increment
  FOREIGN KEY (id) REFERENCES Commentable(id)
) TYPE=InnoDB;

-- similar for Photos and Events.

Define the Commentstable 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 Commentabletoo. So too for Photosand Events.

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 1
INSERT INTO Articles (id, ...) VALUES ( LAST_INSERT_ID(), ... );

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 2
INSERT INTO Photos (id, ...) VALUES ( LAST_INSERT_ID(), ... );

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 3
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;
```
Then you'd need to run a second query to get data from the respective resource table (Photos, Articles, etc.), after discovering from commentable_typewhich table to join to. You can't do it in the same query, because SQL requires that tables be named explicitly; you can't join to a table determined by data results in the same query.

定义Commentable为真正的 SQL 表，而不仅仅是 Rails 模型定义中的形容词。不需要其他列。
```
CREATE TABLE Commentable (
  id INT AUTO_INCREMENT PRIMARY KEY
) TYPE=InnoDB;
```

将表Articles、Photos、和定义Events为的“子类” Commentable，使它们的主键也是引用的外键Commentable。

CREATE TABLE Articles (
  id INT PRIMARY KEY, -- not auto-increment
  FOREIGN KEY (id) REFERENCES Commentable(id)
) TYPE=InnoDB;

-- similar for Photos and Events.

Comments使用外键定义表Commentable。

CREATE TABLE Comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  commentable_id INT NOT NULL,
  FOREIGN KEY (commentable_id) REFERENCES Commentable(id)
) TYPE=InnoDB;

当您想创建一个Article（例如）时，您也必须在其中创建一个新行Commentable。对于Photos和也是如此Events。

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 1
INSERT INTO Articles (id, ...) VALUES ( LAST_INSERT_ID(), ... );

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 2
INSERT INTO Photos (id, ...) VALUES ( LAST_INSERT_ID(), ... );

INSERT INTO Commentable (id) VALUES (DEFAULT); -- generate a new id 3
INSERT INTO Events (id, ...) VALUES ( LAST_INSERT_ID(), ... );

当您要创建时Comment，请使用中存在的值Commentable。

INSERT INTO Comments (id, commentable_id, ...)
VALUES (DEFAULT, 2, ...);

当你想查询给定的评论时Photo，做一些连接：

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;

当您只有评论的 id 并且您想找到它是评论的可评论资源时。为此，您可能会发现 Commentable 表指定它引用的资源很有帮助。
```
SELECT commentable_id, commentable_type FROM Commentable t
JOIN Comments c ON (t.id = c.commentable_id)
WHERE c.id = 42;
```
然后，在发现commentable_type要加入的表后，您需要运行第二个查询以从相应的资源表（照片、文章等）中获取数据。您不能在同一个查询中执行此操作，因为 SQL 要求显式命名表；您不能加入由同一查询中的数据结果确定的表。

Admittedly, some of these steps break the conventions used by Rails. But the Rails conventions are wrong with respect to proper relational database design.

诚然，其中一些步骤违反了 Rails 使用的约定。但是 Rails 约定在正确的关系数据库设计方面是错误的。

Answer 2

回答by Eric Anderson

Bill Karwin is correct that foreign keys cannot be used with polymorphic relationships due to SQL not really having a native concept polymorphic relationships. But if your goal of having a foreign key is to enforce referential integrity you can simulate it via triggers. This gets DB specific but below is some recent triggers I created to simulate the cascading delete behavior of a foreign key on a polymorphic relationship:

Bill Karwin 是正确的，外键不能与多态关系一起使用，因为 SQL 没有真正具有原生概念多态关系。但是，如果您拥有外键的目标是强制执行参照完整性，您可以通过触发器模拟它。这让 DB 特定，但下面是我最近创建的一些触发器，用于模拟多态关系上外键的级联删除行为：

CREATE FUNCTION delete_related_brokerage_subscribers() RETURNS trigger AS $$
  BEGIN
    DELETE FROM subscribers
    WHERE referrer_type = 'Brokerage' AND referrer_id = OLD.id;
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_brokerage_subscriber_delete
AFTER DELETE ON brokerages
FOR EACH ROW EXECUTE PROCEDURE delete_related_brokerage_subscribers();


CREATE FUNCTION delete_related_agent_subscribers() RETURNS trigger AS $$
  BEGIN
    DELETE FROM subscribers
    WHERE referrer_type = 'Agent' AND referrer_id = OLD.id;
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER cascade_agent_subscriber_delete
AFTER DELETE ON agents
FOR EACH ROW EXECUTE PROCEDURE delete_related_agent_subscribers();

In my code a record in the brokeragestable or a record in the agentstable can relate to a record in the subscriberstable.

在我的代码中，brokerages表中的记录或表中的记录agents可以与表中的记录相关subscribers。

Ruby-on-rails 为什么在多态关联中不能有外键？

提问by eggdrop

回答by Bill Karwin

回答by Eric Anderson

相关推荐

最近更新

标签

Ruby-on-rails 为什么在多态关联中不能有外键？

提问by eggdrop

回答by Bill Karwin

回答by Eric Anderson

相关推荐

Ruby-on-rails 如何使用散列数组填充 select_tag？

Ruby-on-rails Rails中has_one和belongs_to的区别？

Ruby-on-rails 如何在 rails 表单中有一个下拉 <select> 字段？

Ruby-on-rails 有没有办法避免自动更新 Rails 时间戳字段？

相关推荐

最近更新

标签