[SOLVED] Ensuring consistent common reference among two tables

Ensuring consistent common reference among two tables

SQLite schema:

CREATE TABLE Lots (
    Id INTEGER PRIMARY KEY
);

CREATE TABLE Parts (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots
);

CREATE TABLE Inspections (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots,
    PartId INTEGER NULL REFERENCES Parts
);

Each Lot has associated Parts and Inspections, but some of the Inspections are associated with a specific part as well. The problem is that there's nothing ensuring that an Inspection and its referenced Part both reference the same Lot.

I use something like this as a local data store in my app so I'm not worried about these theoretical inconsistencies, but I am interested in learning how to handle this properly.

Solution

You can add an additional UNIQUE index in the part table that includes the LotId column:

CREATE UNIQUE INDEX i1 ON Parts(Id, LotId)

And then you can reference this new "super key" in your foreign key constraint:

CREATE TABLE Inspections (
    Id INTEGER PRIMARY KEY,
    LotId INTEGER NOT NULL REFERENCES Lots,
    PartId INTEGER NULL REFERENCES Parts,
    FOREIGN KEY (PartId, LotId) REFERENCES Parts (Id, LotId)
);

Whether you then choose to not include the simple FK on just PartId is up to you.