为什么你不能在多态关联中有外键？ [英] 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.

有几种选择：

• 独占弧：创建多个外键列，每个引用一个父级。

• Exclusive Arcs: Create multiple foreign key columns, each referencing one parent. Enforce that exactly one of these foreign keys can be non-NULL.

反转关系：使用三个多对多

Concrete Supertable：除了隐式的可注释超类之外，还可以创建一个真实的每个父表引用的表。然后将您的评论链接到supertable。伪路径代码将类似于以下（我不是一个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

我还介绍了我的演示文稿中的多态关联 SQL中的实用面向对象模型和我的书 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.

会发生什么，例如，如果您插入一个注释和名称Video作为父表的名称注释？没有名为视频的表存在。是否应该中止插入错误？什么约束被违反？ 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 表，在注释中指示事件作为其父级的行，应该是什么结果？应该中止删除表吗？应该将注释中的行孤立吗？是否应该更改为引用另一个现有表，例如 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.

• 定义 code>作为真正的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 可评论的子类照片和通过使其主键也是引用 Commentable 的外键。

    CREATE TABLE Articles (
      id INT PRIMARY KEY, -- not auto-increment
      FOREIGN KEY (id) REFERENCES Commentable(id)
    ) TYPE=InnoDB;
    
    -- similar for Photos and Events.
    

  
  

  • 定义一个外键可评注。

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

  
  

  • 当您要创建文章（例如），你必须在 Commentable 中创建一个新行。 照片和活动也如此。

  • 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(), ... );
    

  
  

  • 当您要创建注释，使用 Commentable 中存在的值。

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

  
  

  • 当您想要查询给定 code>，做一些连接：

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

  
  

  诚然，其中一些步骤违反了约定使用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屋！