Warm tip: This article is reproduced from serverfault.com, please click

Sqlite foreign key mismatch?

发布于 2020-11-28 06:28:21

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)
);
Questioner
julie
Viewed
0
forpas 2020-11-28 16:58:09

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.