Search code examples
sqliteforeign-keysentity-relationship

Sqlite foreign key mismatch?


I've read question What is causing this sqlite foreign key mismatch? and understood the referenced foreign keys to be unique, but insertions to table are still throwing foreign key mismatch errors:

CREATE TABLE medication (
med_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
med_name VARCHAR (20) NOT NULL, 
dosage VARCHAR (10)
);    

CREATE TABLE disease (
dis_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
disease_name VARCHAR (20) NOT NULL
);    

CREATE TABLE dis_med (
disease_id int NOT NULL, 
medication_id int NOT NULL, 
CONSTRAINT PK_dis_med PRIMARY KEY (disease_id, medication_id), 
CONSTRAINT FK_dis FOREIGN KEY (disease_id) REFERENCES disease (dis_id), 
CONSTRAINT FK_med FOREIGN KEY (medication_id) REFERENCES medication (med_id));

CREATE TABLE user_disease (
user_id REFERENCES user (user_id), 
dis_id REFERENCES disease (dis_id), 
med_id REFERENCES dis_med(medication_id),
CONSTRAINT PK_dis_user PRIMARY KEY (user_id, dis_id)
);

Per the list in the question I cited:

  • the parent table (medication, disease) exists.
  • the parent columns exist
  • the child table references all of the primary key columns in the parent table

Solution

  • From SQLite Foreign Key Support/3. Required and Suggested Database Indexes:

    Usually, the parent key of a foreign key constraint is the primary key of the parent table.
    If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

    With this:

    CREATE TABLE user_disease (
    ...........................
    med_id REFERENCES dis_med(medication_id),
    ...........................
    );
    

    the column med_id of user_disease references the column medication_id of dis_med, which is not the PRIMARY KEY of dis_med and there is no UNIQUE constraint for it. It just references med_id of medication .

    Why do you need the column med_id in user_disease?
    You have dis_id referencing disease, which may also be used to retrieve from dis_med (all) the row(s) from dis_med for that disease.