tablas - MySQL-Restricciones de clave foránea condicional

llave foranea compuesta mysql

Question

Tengo la siguiente tabla de "comentarios" en mi aplicación:

comments
--------
id           INT
foreign_id   INT
model        TEXT
comment_text TEXT
...

la idea de esta tabla es almacenar comentarios para varias partes de mi aplicación; puede almacenar comentarios para la publicación de blog, es decir:

1|34|blogpost|lorem ipsum...

foto del usuario:

2|12|picture|lorem ipsum...

y así.

ahora, ¿hay alguna manera de forzar la restricción FOREIGN KEY en tales datos?

es decir algo como esto en la tabla de comentarios:

FOREIGN KEY (`foreign_id`) REFERENCES blogposts (`id`)
//but only when model=''blogpost''

Answer 1

Está intentando hacer un diseño que se llama Asociaciones polimórficas . Es decir, la clave externa puede referenciar filas en cualquiera de varias tablas relacionadas.

Pero una restricción de clave externa debe hacer referencia exactamente a una tabla. No puede declarar una clave externa que haga referencia a tablas diferentes según el valor en otra columna de su tabla de Comments . Esto violaría varias reglas del diseño de bases de datos relacionales.

Una mejor solución es hacer una especie de "supertable" a la que hagan referencia los comentarios.

CREATE TABLE Commentable (
  id SERIAL PRIMARY KEY
);

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  foreign_id INT NOT NULL,
  ...
  FOREIGN KEY (foreign_id) REFERENCES Commentable(id)
);

Cada uno de sus tipos de contenido se consideraría un subtipo de esta supertabliga. Esto es análogo al concepto orientado a objetos de una interfaz .

CREATE TABLE BlogPosts (
  blogpost_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (blogpost_id) REFERENCES Commentable(id)
);

CREATE TABLE UserPictures (
  userpicture_id INT PRIMARY KEY, -- notice this is not auto-generated
  ...
  FOREIGN KEY (userpicture_id) REFERENCES Commentable(id)
);

Antes de poder insertar una fila en BlogPosts o UserPictures , debe insertar una nueva fila en Commentable para generar una nueva identificación de pseudokey. Luego puede usar esa ID generada a medida que inserta el contenido en la tabla de subtipo respectiva.

Una vez que haga todo eso, puede confiar en las restricciones de integridad referencial.