为什么在多态关联中不能有外键? [英] Why can you not have a foreign key in a polymorphic association?

问题描述

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

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

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

推荐答案

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

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.

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

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

有几种选择:

• 独占弧:创建多个外键列，每个外键列引用一个父列.强制这些外键中的一个可以是非 NULL.

• 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.

具体超级表:创建一个真实的表，而不是隐含的可评论"超类，您的每个父表都引用该表.然后将您的评论链接到该超级表.伪 Rails 代码类似于以下内容(我不是 Rails 用户，因此将其视为指南，而不是文字代码):

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-OrientedSQL 中的模型，以及我的书 SQL 反模式:避免数据库编程的陷阱.

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 中的外键不支持这种设计.

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.

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

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?

同样，如果删除 Events 表，但 Comments 中有行表明 Events 作为其父级，结果应该是什么?应该中止删除表吗?Comments 中的行应该是孤立的吗?它们是否应该更改为引用另一个现有表，例如 Articles?用于指向 Events 的 id 值在指向 Articles 时是否有意义?

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

这些困境都是因为多态关联依赖于使用数据(即字符串值)来引用元数据(表名).这不受 SQL 支持.数据和元数据是分开的.

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.

我很难围绕你的混凝土超级表"提案.

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

• 将 Commentable 定义为真正的 SQL 表，而不仅仅是 Rails 模型定义中的形容词.不需要其他列.

  • 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;
    

  • 将表Articles、Photos和Events定义为Commentable的子类"，通过使它们的主键也是引用 Commentable 的外键.

  • 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, -- not auto-increment
      FOREIGN KEY (id) REFERENCES Commentable(id)
    ) TYPE=InnoDB;
    
    -- similar for Photos and Events.
    

  • 使用Commentable的外键定义Comments表.

    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 也是如此.

  • 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); -- 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 中的值.

  • When you want to create a Comment, use a value that exists in Commentable.

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

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

  • 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;
    

  • 当您只有评论的 id 并且您想找到它是评论的可评论资源时.为此，您可能会发现 Commentable 表指定它引用的资源很有帮助.

  • 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 要求显式命名表；您不能加入由同一查询中的数据结果确定的表.

    Then you'd need to run a second query to get data from the respective resource table (Photos, Articles, etc.), after discovering from commentable_type which 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.

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

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

    这篇关于为什么在多态关联中不能有外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！