Stack Overflow Asked by daisy on December 8, 2020
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:
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)
);
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.
Answered by forpas on December 8, 2020
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP