Sqlite foreign key mismatch?

Question

I've read this question and understood the referenced foreign keys to be unique, but somehow the insertion 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)
);

Through 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

Update1
I was able to insert data and bypass the error by altering the user_disease table by composite foreign key. I'd appreciate it if someone can point out what's the best design here. Many thanks in advance!

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

forpas · Accepted Answer · 2020-11-28T08:58:09.577

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.

Sqlite foreign key mismatch?

1 Answers1