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.