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...
Thank you for your thoughts!
Dennis
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
