How to set cascade on SQLite database with compound primary foreign key?

Question

I have a db that's structured with a supertype table as well as subtype tables like so:

EVENT
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode )

different types of events have their own tables and have the same primary key, except that it's foreign.

EXERCISE
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ) ON CONFLICT IGNORE,
CONSTRAINT "PrimaryKey" FOREIGN KEY ("PatientId", "EventTypeCode", "DateTime") REFERENCES "Event" ("PatientId", "EventTypeCode", "DateTime") ON DELETE CASCADE ON UPDATE CASCADE

When I try to delete an entry in Event, I get a foreign key mismatch and when I delete it in Exercise it deletes, but only in Exercise.. it does not cascade. What do I need to do to get the cascade to work properly? I would prefer to delete the entry in Event and have it cascade to Exercise.. which from the examples I've seen seems like how it should work...

Selfrealization

Answer 1

It is because you have set up an invalid foreign key. Even though SQLite allows you to set it up, it will error out when trying to enforce it.

http://www.sqlite.org/foreignkeys.html#fk_indexes

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

Foreign keys should be set up against a unique target. The column PatientId references Event(PatientID) is not valid, because PatientID alone in table Event is not unique.

---

EDIT

Foreign key support is only available on SQLite 3.6.19 or above. Depending on what tool you use, you also need to enable pragma foreign_keys explicitly. For SQLite Manager add-on for Firefox for example, see here http://code.google.com/p/sqlite-manager/wiki/ForeignKeys

---

This works for me
Caution!! don't drop your existing tables if they contain anything important. Try on a new db

drop table if exists event;
drop table if exists exercise;

create table EVENT (
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ));
create table EXERCISE(
PatientId INTEGER,
DateTime TEXT,
EventTypeCode TEXT,
PRIMARY KEY( PatientId, DateTime, EventTypeCode ) ON CONFLICT IGNORE,
CONSTRAINT "PrimaryKey" FOREIGN KEY ("PatientId", "EventTypeCode", "DateTime") REFERENCES "Event" ("PatientId", "EventTypeCode", "DateTime") ON DELETE CASCADE ON UPDATE CASCADE);
insert into Event (patientid, datetime, eventtypecode) values (1,2,3);
insert into Event (patientid, datetime, eventtypecode) values (4,5,6);
insert into Event (patientid, datetime, eventtypecode) values (7,9,8);
insert into Exercise (patientid, datetime, eventtypecode) values (1,2,3);
insert into Exercise (patientid, datetime, eventtypecode) values (7,9,8);

delete from event where patientid=1;

Unhallowed

none of the fields are necessarily unique in and of themselves, hence the compound key... all 3 will definitely be unique. Is there any way to make this work? could I remove the FK from patient_id in event? that would be inaccurate, though. – Selfrealization

@Selfrealization If you mean to set a FK on 3 fields, set up a one(1) FK as such, not three(3) individual FKs. – Unhallowed

Ah k.. didn't realize that was the way to do it, but I'm still having issues with the cascade.. I updated the code and description of results in my questions if you could have a look? – Selfrealization

@Selfrealization - please see expanded answer – Unhallowed

That's the same code I had tested on.. i'm still getting a foreign key mismatch... I assume that does indicate that foreign keys are being supported? – Selfrealization

@Selfrealization same down to the exact values being inserted? What tool are you using with SQLite? – Unhallowed

yeah i removed what i had and readded with your code to be sure. i'm using the demo of navicat right now. was having trouble finding good free sqlite tools that support foreign keys – Selfrealization

@Selfrealization I use SQLite Manager addon for Firefox. What's the SQLite version for Navicat? FKs only work for 3.6.19+. May be an unrelated issue in Navicat since the code is otherwise ok? – Unhallowed

3.7.3 according to the properties; strange. – Selfrealization

@Selfrealization If you used the code above verbatim and it doesn't work, I would file a bug report pasting the code, because it should work. – Unhallowed

i'm enabled foreign keys in sqlite manager and am getting the same FK error with the code you provided (i wasn't before fks enabled) – Selfrealization

@Selfrealization - I have updated it a bit to insert 3 into event, 2 into exercise and delete 1 from event (taking the exercise with it). The code looks right and works, so there is either a setting I am unaware of, or there is a bug in Navicat. Hopefully someone will come along to clarify – Unhallowed

@Richard aka cyberkiwi: It works for me under 3.7.4, but only if I use pragma foreign_keys=on, which is what I expected. Foreign key support isn't on by default when you compile the SQLite amalgamation. I can't recall whether that's under control of a compile-time setting. (Too tired to look tonight.) – Discomposure