es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

Modelado de claves foráneas de tabla de enlace Many-to-Many en SQLite.

11 Jun, 2023 Programación

Tengo las dos siguientes tablas en SQLite:

CREATE TABLE Link (
    link_id   integer NOT NULL,
    part_id   integer NOT NULL,
    CONSTRAINT link_pk PRIMARY KEY(link_id, part_id)
);
CREATE TABLE Main (
    main_id   integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    link_id   integer NOT NULL REFERENCES Link(link_id)
);
INSERT INTO Link (link_id, part_id) VALUES (1,10);
INSERT INTO Link (link_id, part_id) VALUES (1,11);
INSERT INTO Link (link_id, part_id) VALUES (1,12);
INSERT INTO Link (link_id, part_id) VALUES (2,15);

INSERT INTO Main (main_id, link_id) VALUES (1,1);
INSERT INTO Main (main_id, link_id) VALUES (2,1);
INSERT INTO Main (main_id, link_id) VALUES (3,2);

Muchas filas de Main pueden hacer referencia al mismo link_id, y muchas filas de Link pueden tener el mismo link_id, de tal manera que select * from Main natural join Link where main_id=1 devolverá N filas, y select * from Main where link_id=1 devolverá K filas. El link_id es importante, y los datos originales indican que cada elemento principal (main) tiene 1 link_id, y cada enlace (link) tiene N identificadores de pieza (part_id).

Utilizando los esquemas anteriores, no puedo insertar ninguna fila en Main debido a la restricción de clave externa (foreign key mismatch - "Main" referencing "Link": INSERT INTO Main (main_id, link_id) VALUES (1,1);), presumiblemente debido al requisito de clave compuesta. Puedo ignorar esta restricción eliminando la clave externa, pero en ese caso obviamente estoy omitiendo una restricción. Invertir la dirección de la clave tampoco funcionaría, ya que, como se dijo anteriormente, se trata de una relación de muchos a muchos. ¿Hay alguna manera de modelar esto correctamente en SQLite con una restricción que asegure que al menos una fila exista en Link para cada link_id en Main?

Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Sí, hay una forma de modelar esto correctamente en SQLite con una restricción que al menos una fila exista en Link para cada link_id en Main. Una solución sería agregar una restricción de verificación en la tabla Main para garantizar que el link_id exista en la tabla Link:

    CREATE TABLE `Main` (
    `main_id`   integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    `link_id`   integer NOT NULL,
    CONSTRAINT `main_link_fk` FOREIGN KEY(`link_id`) REFERENCES `Link`(`link_id`),
    CONSTRAINT `main_link_check` CHECK((SELECT COUNT(*) FROM `Link` WHERE `Link`.`link_id` = `Main`.`link_id`) >= 1)
);

    La restricción main_link_check usa una subconsulta para contar el número de filas en la tabla Link donde el link_id coincide con el link_id en la tabla Main. Si este recuento es menor que 1, la restricción de verificación falla y la fila no se puede insertar o actualizar.

    Con esta restricción en su lugar, debería poder insertar filas en la tabla Main sin encontrar ninguna restricción de clave externa. Además, la restricción de verificación asegura que hay al menos una fila correspondiente en la tabla Link para cada link_id en la tabla Main.

Comments are closed.