No dudes en compartir nuestros tutoriales y códigos en tus redes sociales, necesitamos tu ayuda para hacer crecer esta comunidad.
Solución:
No es fácil de hacer en SQL pero no es imposible. Si desea que esto se aplique solo a través de DDL, el DBMS debe haberlo implementado. DEFERRABLE limitaciones. Esto se podría hacer (y se puede verificar que funcione en Postgres, que los ha implementado):
-- lets create first the 2 tables, A and B:
CREATE TABLE a
( aid INT NOT NULL,
bid INT NOT NULL,
CONSTRAINT a_pk PRIMARY KEY (aid)
);
CREATE TABLE b
( bid INT NOT NULL,
aid INT NOT NULL,
CONSTRAINT b_pk PRIMARY KEY (bid)
);
-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
bid INT NOT NULL,
CONSTRAINT r_pk PRIMARY KEY (aid, bid),
CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
);
Hasta aquí está el diseño “normal”, donde cada A puede estar relacionado con cero, uno o muchos B y cada B puede estar relacionado con cero, uno o muchos A.
La restricción de “participación total” necesita restricciones en el orden inverso (de A y B respectivamente, haciendo referencia R). Teniendo FOREIGN KEY restricciones en direcciones opuestas (de X a Y y de Y a X) está formando un círculo (un problema de “huevo y gallina”) y es por eso que necesitamos que uno de ellos sea al menos DEFERRABLE. En este caso tenemos dos círculos (A -> R -> A y B -> R -> B por lo que necesitamos dos restricciones diferibles:
-- then we add the 2 constraints that enforce the "total participation":
ALTER TABLE a
ADD CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
DEFERRABLE INITIALLY DEFERRED ;
ALTER TABLE b
ADD CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
DEFERRABLE INITIALLY DEFERRED ;
Entonces podemos probar que podemos insertar datos. Tenga en cuenta que el INITIALLY DEFERRED no es necesario. Podríamos haber definido las restricciones como DEFERRABLE INITIALLY IMMEDIATE pero luego tendríamos que usar el SET CONSTRAINTS declaración para diferirlos durante la transacción. Sin embargo, en todos los casos, necesitamos insertar en las tablas en una sola transacción:
-- insert data
BEGIN TRANSACTION ;
INSERT INTO a (aid, bid)
VALUES
(1, 1), (2, 5),
(3, 7), (4, 1) ;
INSERT INTO b (aid, bid)
VALUES
(1, 1), (1, 2),
(2, 3), (2, 4),
(2, 5), (3, 6),
(3, 7) ;
INSERT INTO r (aid, bid)
VALUES
(1, 1), (1, 2),
(2, 3), (2, 4),
(2, 5), (3, 6),
(3, 7), (4, 1),
(4, 2), (4, 7) ;
END ;
Probado en SQLfiddle.
Si el DBMS no tiene DEFERRABLE restricciones, una solución es definir el A (bid) y B (aid) columnas como NULL. El INSERT Los procedimientos / declaraciones deberán insertarse primero en A y B (poniendo nulos en bid y aid respectivamente), luego insértelo en R y luego actualice el null valores anteriores a los relacionados no null valores de R.
Con este enfoque, el DBMS no hace cumplir los requisitos de DDL solo, sino que INSERT (y UPDATE y DELETE y MERGE) el procedimiento debe ser considerado y ajustado en consecuencia y los usuarios deben estar restringidos para usar solo ellos y no tener acceso directo de escritura a las tablas.
Tener círculos en el FOREIGN KEY Muchos consideran que las restricciones no son las mejores prácticas y, por buenas razones, la complejidad es una de ellas. Con el segundo enfoque, por ejemplo (con columnas que aceptan valores NULL), la actualización y eliminación de filas aún tendrá que realizarse con código adicional, según el DBMS. En SQL Server, por ejemplo, no puede simplemente poner ON DELETE CASCADE porque las actualizaciones y eliminaciones en cascada no están permitidas cuando hay círculos FK.
Lea también las respuestas a esta pregunta relacionada:
¿Cómo tener una relación de uno a varios con un niño privilegiado?
Otro tercer enfoque (vea mi respuesta en la pregunta mencionada anteriormente) es eliminar los FK circulares por completo. Entonces, manteniendo la primera parte del código (con tablas A, B, R y extranjero keys solo de R a A y B) casi intacto (en realidad simplificándolo), agregamos otra tabla para A para almacenar el artículo relacionado “debe tener uno” de B. Entonces el A (bid) la columna se mueve a A_one (bid) Lo mismo se hace para la relación inversa de B a A:
CREATE TABLE a
( aid INT NOT NULL,
CONSTRAINT a_pk PRIMARY KEY (aid)
);
CREATE TABLE b
( bid INT NOT NULL,
CONSTRAINT b_pk PRIMARY KEY (bid)
);
-- then table R:
CREATE TABLE r
( aid INT NOT NULL,
bid INT NOT NULL,
CONSTRAINT r_pk PRIMARY KEY (aid, bid),
CONSTRAINT a_r_fk FOREIGN KEY (aid) REFERENCES a,
CONSTRAINT b_r_fk FOREIGN KEY (bid) REFERENCES b
);
CREATE TABLE a_one
( aid INT NOT NULL,
bid INT NOT NULL,
CONSTRAINT a_one_pk PRIMARY KEY (aid),
CONSTRAINT r_a_fk FOREIGN KEY (aid, bid) REFERENCES r
);
CREATE TABLE b_one
( bid INT NOT NULL,
aid INT NOT NULL,
CONSTRAINT b_one_pk PRIMARY KEY (bid),
CONSTRAINT r_b_fk FOREIGN KEY (aid, bid) REFERENCES r
);
La diferencia con el primer y segundo enfoque es que no hay FK circulares, por lo que las actualizaciones y eliminaciones en cascada funcionarán bien. La aplicación de la “participación total” no es solo por DDL, como en el segundo enfoque, y debe realizarse mediante los procedimientos adecuados (INSERT/UPDATE/DELETE/MERGE). Una pequeña diferencia con el segundo enfoque es que todas las columnas pueden definirse sin que se puedan aceptar valores NULL.
Otro, cuarto enfoque (ver La respuesta de @Aaron Bertrand en la pregunta mencionada anteriormente) es utilizar filtrado / parcial índices únicos, si están disponibles en su DBMS (necesitaría dos de ellos, en R tabla, para este caso). Esto es muy similar al tercer enfoque, excepto que no necesitará las 2 tablas adicionales. La restricción de “participación total” aún debe aplicarse por código.
No puedes directamente. Para empezar, no podría insertar el registro para A sin un B ya existente, pero no podría crear el registro B si no hay un registro A para él. Hay varias formas de imponerlo usando cosas como disparadores: tendría que verificar en cada inserción y eliminar que al menos un registro correspondiente permanezca en la tabla de enlaces AB.
Recuerda compartir este enunciado si si solucionó tu problema.