I have an sqlite database structured as follows:
CREATE TABLE IF NOT EXISTS Patient
( PatientId INTEGER PRIMARY KEY AUTOINCREMENT );
CREATE TABLE IF NOT EXISTS Event
(
PatientId INTEGER REFERENCES Patient( PatientId ),
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )
);
CREATE TABLE IF NOT EXISTS Reading
(
PatientId INTEGER REFERENCES Patient( PatientId ),
DateTime TEXT REFERENCES Event (DateTime),
EventTypeCode TEXT REFERENCES Event (EventTypeCode),
Value REAL,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )
);
I insert a Patient with Id #1
then I run:
INSERT INTO Event (PatientId, DateTime, EventTypeCode) VALUES (1, '2011-01-23 19:26:59', 'R')
which works
then I run:
INSERT INTO Reading (PatientId, DateTime, EventTypeCode, Value) VALUES (1, '2011-01-23 19:26:59', 'R', 7.9)
and it gives me a foreign key mismatch. Patient Id is '1' in all cases, and the datetime and typecodes match in the 2nd and 3rd queries. I do not understand what is mismatching, but I'm a bit new to actually defining foreign keys and i do not know what I am doing wrong.
I'm not familiar with SQLite but a little Google'ing turned up this. The documentation says
If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are "DML errors" and errors reported when the schema is changed are "DDL errors". So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually "foreign key mismatch" but can also be "no such table" if the parent table does not exist. Foreign key DML errors are may be reported if:
- The parent table does not exist, or
- The parent key columns named in the foreign key constraint do not exist, or
- The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or
- The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.
I suspect you might be running into #3 in that list.
Also, while other DBs might support using a non-unique index as a foreign key reference, (see answers here), it's a bad design choice in my opinion. I would restructure so that either
Reading.PatientId
references Event.PatientId
so that the complete composite key from Event
is referenced by Reading
or,EventId
auto-increment, primary key to the Event
table and use that as the foreign key in the Reading
table (so that you only have EventId
and Value
under Reading
and you can get the PatientId, DateTime, EventTypeCode
out of Event
).I'd suggest #2 so that you can avoid the redundancy of PatientId, DateTime
and EventTypeCode
in both Event
and Reading
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With