Dlaczego nie można mieć klucza obcego w związku polimorficznym?

Klucz obcy musi odwoływać się tylko do jednej tabeli nadrzędnej. Jest to fundamentalne zarówno dla składni SQL, jak i teorii relacyjnej.

Asocjacja polimorficzna jest wtedy, gdy dana kolumna może odwoływać się do jednej z dwóch lub więcej tabel nadrzędnych. Nie ma możliwości zadeklarowania tego ograniczenia w SQL.

Projektowanie Asocjacji polimorficznych łamie zasady projektowania relacyjnych baz danych. Nie polecam go używać.

Istnieje kilka alternatywy:

• Exclusive Arcs: tworzy wiele kolumn klucza obcego, z których każda odwołuje się do jednego rodzica. Wymuś, że dokładnie jeden z tych kluczy obcych może być inny niż NULL.

• Odwróć relację: użyj trzech tabel wielu do wielu, każda odwołuje się do komentarzy i odpowiedniego rodzica.

• Concrete Supertable: zamiast domyślnej superklasy "komentowalnej", stwórz prawdziwą tabelę, którą każdy z rodziców odniesienia do tabel. Następnie link swoje komentarze do tego supertable. Kod Pseudo-rails byłby podobny do następującego (nie jestem użytkownikiem Rails, więc traktuj to jako wytyczne, a nie dosłowny kod): {]}

  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
  

Opisuję również skojarzenia polimorficzne w mojej prezentacji praktyczne modele obiektowe w SQL oraz w mojej książce SQL Antipatterns: unikanie pułapek programowania baz danych .

Re twój komentarz: tak, Wiem, że jest inny kolumna wskazująca nazwę tabeli, na którą rzekomo wskazuje klucz obcy. Ten projekt nie jest obsługiwany przez klucze obce w SQL.

Co się stanie, na przykład, jeśli wstawisz komentarz i nazwę "wideo" jako nazwę tabeli nadrzędnej dla tego Comment? Nie istnieje żadna tabela o nazwie "Video". Czy wstawka powinna zostać przerwana z błędem? Jakie ograniczenia są naruszane? Skąd RDBMS wie, że ta kolumna ma nazywać istniejącą tabelę? Jak radzi sobie z rozróżnianiem wielkości liter nazwy stolików?

Podobnie, jeśli upuścisz Events tabelę, ale masz wiersze w Comments, które wskazują zdarzenia jako ich rodzica, jaki powinien być wynik? Czy stół zrzutowy powinien zostać przerwany? Czy wiersze w Comments powinny być osierocone? Czy należy zmienić odniesienie do innej istniejącej tabeli, takiej jak Articles? Czy wartości id, które wskazywały na Events, mają jakiś sens przy wskazywaniu na Articles?

Dylematy te wynikają z faktu, że Asocjacje polimorficzne zależą od wykorzystania danych (tj. wartość łańcuchowa), aby odnosić się do metadanych (nazwy tabeli). Nie jest to obsługiwane przez SQL. Dane i metadane są oddzielne.

Trudno mi się skupić na twojej propozycji "betonowej Supertowalnej".

• Zdefiniuj Commentable jako prawdziwą tabelę SQL, a nie tylko przymiotnik w definicji modelu Rails. Inne kolumny nie są konieczne.

  CREATE TABLE Commentable (
    id INT AUTO_INCREMENT PRIMARY KEY
  ) TYPE=InnoDB;
  

• Definiowanie tabel Articles, Photos, i Events jako "podklasy" z Commentable, poprzez ich klucz główny jest również kluczem obcym odwołującym się Commentable.

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

• Zdefiniuj Comments tabelę z kluczem obcym do Commentable.

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

• Jeśli chcesz utworzyć Article (na przykład), musisz również utworzyć nowy wiersz w Commentable. Tak też dla Photos i 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(), ... );
  

• Jeśli chcesz utworzyć Comment, Użyj wartości, która istnieje w Commentable.

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

• Gdy chcesz odpytywać komentarze danego Photo, wykonaj kilka dołączył:

  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;
  

• Gdy masz tylko id komentarza i chcesz znaleźć, do jakiego zasobu można go komentować, jest to komentarz. W tym celu pomocne może okazać się wskazanie przez tabelę Komentowalną zasobu, do którego się odwołuje.

  SELECT commentable_id, commentable_type FROM Commentable t
  JOIN Comments c ON (t.id = c.commentable_id)
  WHERE c.id = 42;
  

  Następnie trzeba uruchomić drugie zapytanie, aby uzyskać dane z odpowiedniej tabeli zasobów (zdjęcia, artykuły, itp.), po odkryciu z commentable_type, do której tabeli należy dołączyć. Nie można tego zrobić w tym samym zapytaniu, ponieważ SQL wymaga tabele są nazwane jawnie; nie można połączyć się z tabelą określoną przez dane wyniki w tym samym zapytaniu.