ruby-on-rails - secundaria - restricciones de nombres que puede tener una base de datos

¿Por qué no puedes tener una clave externa en una asociación polimórfica?

Question

¿Por qué no puede tener una clave externa en una asociación polimórfica, como la que se representa a continuación como modelo de 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

Bill Karwin tiene razón en que las claves externas no se pueden usar con relaciones polimórficas debido a que SQL no tiene realmente un concepto nativo de relaciones polimórficas. Pero si su objetivo de tener una clave externa es forzar la integridad referencial, puede simularlo a través de desencadenantes. Esto hace que DB sea específico, pero a continuación se muestran algunos desencadenantes recientes que he creado para simular el comportamiento de eliminación en cascada de una clave externa en una relación polimórfica:

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

En mi código, un registro en la tabla de brokerages o un registro en la tabla de agents puede relacionarse con un registro en la tabla de subscribers .

Answer 2

Una clave externa debe hacer referencia solo a una tabla principal. Esto es fundamental tanto para la sintaxis SQL como para la teoría relacional.

Una Asociación polimórfica es cuando una columna determinada puede hacer referencia a una o más de las tablas principales. No hay forma de que puedas declarar esa restricción en SQL.

El diseño de Asociaciones polimórficas rompe las reglas del diseño de bases de datos relacionales. No recomiendo usarlo.

Hay varias alternativas:

• Arcos exclusivos: crea múltiples columnas de clave externa, cada una haciendo referencia a un padre. Haga cumplir que exactamente una de estas claves foráneas puede ser no NULL.

• Invierta la relación: use tres tablas de muchos a muchos, cada uno de los comentarios de referencias y un padre respectivo.

• Concrete Supertable: En lugar de la superclase implícita "commentable", crea una tabla real a la que hace referencia cada una de tus tablas padre. Luego vincula tus Comentarios a esa supertable. El código de pseudo-rails sería algo como lo siguiente (no soy un usuario de Rails, así que trátelo como una guía, no como código literal):

  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

También cubro las asociaciones polimórficas en mi presentación Modelos prácticos orientados a objetos en SQL , y mi libro SQL Antipatterns: Cómo evitar las trampas de la programación de bases de datos .

Re su comentario: Sí, sí sé que hay otra columna que anota el nombre de la tabla que supuestamente señala la clave externa. Este diseño no es compatible con claves externas en SQL.

¿Qué sucede, por ejemplo, si inserta un Comentario y nombra "Video" como el nombre de la tabla principal para ese Comment ? No existe una tabla llamada "Video". ¿Debería abortarse la inserción con un error? ¿Qué restricción se está violando? ¿Cómo sabe el RDBMS que se supone que esta columna debe nombrar una tabla existente? ¿Cómo maneja los nombres de tabla insensibles a mayúsculas y minúsculas?

Del mismo modo, si suelta la tabla de Events , pero tiene filas en Comments que indican Eventos como su principal, ¿cuál debería ser el resultado? ¿Debería abortarse la tabla desplegable? ¿Deben quedar huérfanas las filas en Comments ? ¿Deberían cambiar para referirse a otra tabla existente como Articles ? ¿Los valores de id que solían apuntar a Events tienen algún sentido cuando se apunta a Articles ?

Estos dilemas se deben al hecho de que las Asociaciones polimórficas dependen del uso de datos (es decir, un valor de cadena) para referirse a metadatos (un nombre de tabla). Esto no es compatible con SQL. Los datos y metadatos están separados.

Me está costando pasar por alto su propuesta de "Supertable concreto".

• Defina Commentable como una tabla SQL real, no solo como un adjetivo en la definición del modelo de Rails. No hay otras columnas son necesarias.

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

• Defina las tablas Articles , Photos y Events como "subclases" de Commentable , haciendo que su clave principal sea también una clave foránea que haga referencia a Commentable .

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

• Defina la tabla de Comments con una clave foránea a Commentable .

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

• Cuando desee crear un Article (por ejemplo), debe crear una nueva fila en Commentable también. Lo mismo para Photos y 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(), ... );

• Cuando desee crear un Comment , use un valor que exista en Commentable .

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

• Cuando desee consultar los comentarios de una Photo determinada, realice algunas combinaciones:

  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;

• Cuando solo tiene la identificación de un comentario y quiere encontrar el recurso comentable para el que es un comentario. Para esto, puede encontrar que es útil para la tabla Commentable designar a qué recurso hace referencia.

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

  Entonces necesitaría ejecutar una segunda consulta para obtener datos de la tabla de recursos respectiva (Fotos, Artículos, etc.), después de descubrir de commentable_type qué tabla unirse. No puede hacerlo en la misma consulta, porque SQL requiere que las tablas se nombren explícitamente; no puede unirse a una tabla determinada por los resultados de datos en la misma consulta.

Es cierto que algunos de estos pasos rompen las convenciones utilizadas por Rails. Pero las convenciones de Rails son incorrectas con respecto al diseño de base de datos relacional apropiado.