CODEWITHSUNDEEP
sqliteforeign-keysentity-relationship
julie
julie

Reputation: 55

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:

Upvotes: 1

Views: 995

Answers (1)

forpas
forpas

Reputation: 164089

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.

Upvotes: 1

Related Questions