Many-to-Many Link Table Foreign Key Modeling in SQLite

Question

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?

Answer 1

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.