On Oct 15, 2009, at 10:30 AM, Dennis Volodomanov wrote: > Hello all, > > I'm implementing the new FOREIGN KEY support in our database and I > have this small problem/question. > > Before I had triggers to take care of maintaining deletion of data > that's not referenced by any records, but I can't seem to reproduce > the same behavior with just the foreign key commands. Maybe I'm > missing something. > > So, what I had was: > > CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID INTEGER); > CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE); > > Where DataID in TableA is ID in TableData. > > I also had the following triggers: > > CREATE TRIGGER TriggerADelete AFTER DELETE ON TableA WHEN > OLD.DataID<>0 > BEGIN > DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID > NOT IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1); > END; > > CREATE TRIGGER TriggerAUpdate AFTER UPDATE ON TableA WHEN > OLD.DataID<>0 > BEGIN > DELETE FROM TableData WHERE TableData.ID=OLD.DataID AND OLD.DataID > NOT IN (SELECT DataID FROM TableA WHERE DataID=OLD.DataID LIMIT 1); > END; > > That kept the TableData empty of any non-referenced values. > > What I've got now is (which doesn't work as I expected it to): > > CREATE TABLE TableA(ID INTEGER PRIMARY KEY, SomeData, DataID > INTEGER, FOREIGN KEY(DataID) REFERENCES TableData(ID) ON DELETE > CASCADE ON UPDATE CASCADE); > CREATE TABLE TableData(ID INTEGER PRIMARY KEY, Data UNIQUE); > > Is there a way to reproduce the same without using triggers? If not, > I'm not sure what the advantage of using foreign keys is in my case...
I don't think the triggers you are using can be implemented using foreign keys. Your triggers are basically reference counting (or garbage collecting, whatever you want to call it) - "when the number of references to a data item drops to zero, delete the data item". You could add a foreign key to this schema to prevent the DataID column of TableA from being populated with an invalid id (one that does not refer to any id of the TableData table). Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
