How to set cascade on SQLite database with compoun

问题:

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...

回答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;