I have the following two tables in 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);
Many Main
rows may reference the same link id, and many Link
rows may have the same link id, such that select * from Main natural join Link where main_id=1
will return N rows, and select * from Main where link_id=1
will return K rows. The link id is important, and the original data each main has 1 link id, and each link has N part ids.
Using the schemas above, I am unable to insert any rows in Main
due to the foreign key constraint (foreign key mismatch - "Main" referencing "Link": INSERT INTO Main (main_id, link_id) VALUES (1,1);
), presumably because of the composite key requirement. I can get this to work by removing the foreign key constraint, but then I am obviously missing a constraint. Reversing the direction of the key wouldn't work either since, as stated above, it's a Many-to-Many relationship. Is there a way to properly model this in SQLite with a constraint that at least one row exists in Link
for each link_id
in Main
?
CodePudding user response:
I would propose a different design.
Each of the 2 entities link_id
and part_id
should be the primary key in 2 tables, something like:
CREATE TABLE Links (
link_id INTEGER PRIMARY KEY,
link_description TEXT
);
CREATE TABLE Parts (
part_id INTEGER PRIMARY KEY,
part_description TEXT
);
Then, create the junction table of the above tables (like your current Link
table):
CREATE TABLE Links_Parts (
link_id INTEGER NOT NULL REFERENCES Links(link_id),
part_id INTEGER NOT NULL REFERENCES Parts(part_id),
PRIMARY KEY(link_id, part_id)
);
and the table Main
:
CREATE TABLE Main (
main_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
link_id INTEGER NOT NULL REFERENCES Links(link_id)
);
All the relations are there and you have referential integrity guaranteed if you set foreign key support:
PRAGMA foreign_keys = ON;
See a simplified demo.