[sqlite] cascade-delete doesn't seem to work

[Chris Wolf]

I am trying to get child records to automatically delete upon deletion
of their parent record,
as described here:

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

Unfortunately, it's not deleting the child records.  Either with pragma
foreign_keys on or off.

Thanks for any help,

   -Chris

Here's the sample I'm working with:

drop table if exists artist;
CREATE TABLE artist(
  artistid    INTEGER PRIMARY KEY,
  artistname  TEXT
);

drop table if exists track;
CREATE TABLE track(
  trackid     INTEGER,
  trackname   TEXT,
  trackartist INTEGER REFERENCES artist(artistid) ON DELETE CASCADE
);

insert into artist (artistname) values('Dean Martin');
insert into artist (artistname) values('Frank Sinatra');
insert into track values(11, "That's Amore", 1);
insert into track values(12, "Christmes Blues", 1);
insert into track values(11, "My Way", 2);

delete from artist where artistid=2;

select * from track;
select '"My Way" should have been cascade-deleted.';

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users