Почему вы не можете иметь внешний ключ в полиморфной ассоциации?

Question

Почему вы не можете иметь внешний ключ в полиморфной ассоциации, такой как тот, который представлен ниже как модель 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

Внешний ключ должен ссылаться только на одну родительскую таблицу. Это фундаментально как для синтаксиса SQL, так и для теории отношений.

Полиморфная ассоциация - это когда данный столбец может ссылаться на одну или более родительских таблиц. Вы не можете объявить это ограничение в SQL.

Дизайн Полиморфных Ассоциаций нарушает правила проектирования реляционных баз данных. Я не рекомендую использовать это.

Есть несколько альтернатив:

• Исключительные дуги: создайте несколько столбцов внешнего ключа, каждый из которых ссылается на одного родителя. Убедитесь, что ровно один из этих внешних ключей может быть ненулевым.

• Обратное отношение: используйте три таблицы "многие ко многим", каждая ссылается на комментарии и соответствующий родительский элемент.

• Конкретный Супертабильный. Вместо неявного "комментируемого" суперкласса создайте реальную таблицу, на которую ссылается каждая из ваших родительских таблиц. Затем свяжите ваши комментарии с этим суперстабильным. Код псевдо-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
  

Я также рассматриваю полиморфные ассоциации в своей презентации " Практические объектно-ориентированные модели в SQL" и в своей книге " Антипаттерны SQL: как избежать ловушек при программировании баз данных".

---

Ваш комментарий: Да, я знаю, что есть еще один столбец, в котором записано имя таблицы, на которую предположительно указывает внешний ключ. Этот дизайн не поддерживается внешними ключами в SQL.

Что произойдет, например, если вы вставите комментарий и имя "Видео" в качестве имени родительской таблицы для этого Comment? Нет таблицы с именем "Видео" существует. Должна ли вставка быть прервана с ошибкой? Какое ограничение нарушается? Как СУРБД узнает, что этот столбец должен называть существующую таблицу? Как он обрабатывает имена таблиц без учета регистра?

Аналогично, если вы бросите Events таблица, но у вас есть строки в Comments которые указывают на события как на своего родителя, каким должен быть результат? Следует ли прекратить удаление таблицы? Должны ли строки в Comments быть сиротой? Должны ли они изменить ссылку на другую существующую таблицу, такую ​​как Articles? Есть ли значения идентификаторов, которые указывали на Events имеет смысл, когда указывает на Articles?

Все эти дилеммы связаны с тем фактом, что полиморфные ассоциации зависят от использования данных (то есть строковых значений) для ссылки на метаданные (имя таблицы). Это не поддерживается SQL. Данные и метаданные являются отдельными.

---

Я с трудом нахожу голову над твоим предложением "Конкретный супертейбл".

• определять 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;
  

• Если у вас есть только идентификатор комментария, и вы хотите найти, для какого комментируемого ресурса это комментарий. Для этого вы можете обнаружить, что для таблицы 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 требует, чтобы таблицы были названы явно; вы не можете присоединиться к таблице, определяемой результатами данных в одном запросе.

Следует признать, что некоторые из этих шагов нарушают соглашения, используемые Rails. Но соглашения Rails неверны в отношении правильного проектирования реляционных баз данных.

Answer 2

Билл Карвин прав, что внешние ключи нельзя использовать с полиморфными отношениями из-за того, что у SQL на самом деле нет родной концепции полиморфных отношений. Но если ваша цель иметь внешний ключ - обеспечить ссылочную целостность, вы можете смоделировать его с помощью триггеров. Это зависит от БД, но ниже приведены некоторые последние триггеры, которые я создал для имитации каскадного удаления внешнего ключа при полиморфных отношениях:

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();

В моем коде запись в brokerages таблица или запись в agents таблица может относиться к записи в subscribers Таблица.

Answer 3

Допустим, вы хотите создать подобную систему на SQL. в полиморфной ассоциации вы создаете таблицу LIKE с этими столбцами:

      id_of_like    user_id     liked_id   liked_type
  1            12          3          image
  2            3           5          video

относится к идентификатору изображения или идентификатору видео.

Обычно при создании таблицы вы устанавливаетеforeign keyи сообщите базе данных SQL, на какую таблицу будет ссылаться этот внешний ключ. Прежде чем вы вставите строку, ваша база данных проверит, является ли текущее значение внешнего ключа допустимым или нет. например, если в таблице, где внешний ключ ссылается на таблицу USER, ваша база данных проверит, существует ли текущий внешний ключ в таблице USER. Эта проверка гарантирует, что ваша база данных непротиворечива.

Допустим, в приведенной выше таблице мы каким-то образом установилиliked_idкак ВНЕШНИЙ КЛЮЧ, как база данных узнает, какую таблицу (ИЗОБРАЖЕНИЕ или ВИДЕО) посетить для проверки текущего значения ключа?liked_typeне для менеджера баз данных, это для людей, чтобы читать его, менеджер баз данных не читает его, чтобы проверить, в какую таблицу перейти.

Представьте, что я вставил новую строку сliked_id=333333

      id_of_like    user_id     liked_id   liked_type
  1            12          333333     image

ваш менеджер баз данных не сможет проверить, еслиid=333333существуют в таблице IMAGE или нет.