1

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
byteit101
  • 3,910
  • 2
  • 20
  • 29
  • What kind of rows are you not able to insert? The three rows in the sample can be inserted just OK. – choroba Nov 29 '21 at 20:10
  • You should reconsider your design. `link_id` and `part_id` should be the primary keys in 2 different tables and your current table `Link` should be the junction table of these tables. Then you can have `Main`'s `link_id` to reference the 1st table's `link_id`. – forpas Nov 29 '21 at 20:37
  • @choroba running the code currently gives me: `foreign key mismatch - "Main" referencing "Link": INSERT INTO Main (main_id, link_id) VALUES (1,1);` – byteit101 Nov 29 '21 at 20:53
  • @forpas do you want to expand that into an answer? – byteit101 Nov 29 '21 at 20:54

1 Answers1

3

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Given that links have no description or other attributes, it's so weird having a table with just one column. However, this doesn't enforce links having at least one part. Is there a way to do that? – byteit101 Nov 29 '21 at 22:12
  • @byteit101 Usually all entities like a *link* or a *part* have a description of some kind. Anyway, if it is not needed then you can have a table with only 1 column. But, the constraint that every link should have at least one part can't be enforced by sql only. In your application code you should make sure that you insert a new row in `Links` only if you have a pair of the new inserted link and part to insert in `Links_Parts` as a 2nd step. Also you may need triggers to catch orphaned links after updates or deletes in `Links_Parts`. – forpas Nov 29 '21 at 22:29