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...
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
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/ForeignKeysThis works for me
Caution!! don't drop your existing tables if they contain anything important. Try on a new db