Hi,
as this is my first posting to this list, let me first say thank you for
providing such a great library. It's fun to work with it. I'm intending
to use SQLite in a game engine.
I've encountered some unexpected behavior of foreign keys w.r.t an upsert:
When an entry of the parent table is 'touched' but not deleted or
modified, the child table entry is nevertheless deleted.
Although the foreign key is declared 'ON DELETE CASCADE ON UPDATE
RESTRICT', I did not expect the child entry to be deleted, because the
parent entry was not deleted.
Here is a script that shows the behavior:
-------------------------------------------------------------------------
PRAGMA foreign_keys = ON;
-- Create tables
CREATE TABLE t1 (id char(36), name varchar(40), CONSTRAINT pk_t1 PRIMARY
KEY (id));
CREATE TABLE t2 (id char(36), t1_id char(36), data char(36), CONSTRAINT
pk_t2 PRIMARY KEY (id), CONSTRAINT fk_t1_id FOREIGN KEY (t1_id)
REFERENCES t1(id) ON DELETE CASCADE ON UPDATE RESTRICT DEFERRABLE
INITIALLY DEFERRED);
-- Create table content
INSERT INTO T1 (ID, NAME) VALUES ('A', 'line 1');
INSERT INTO T1 (ID, NAME) VALUES ('B', 'line 2');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-A', 'A', 'abc');
INSERT INTO T2 (ID, T1_ID, DATA) VALUES ('T2-B', 'B', 'xyz');
-- Modify one parent entry of foreign key fk_t1_id
REPLACE INTO T1 (ID, NAME) VALUES ('A', 'line 1-new');
-- Although the parent field was not modified, a cascading delete is
triggered:
SELECT * FROM T2;
-- The cascading delete happens even if the upsert is a no-op:
REPLACE INTO T1 (ID, NAME) VALUES ('B', 'line 2');
SELECT * FROM T2;
------------------------------------------------------------------------
If I understand correctly, the upsert should behave like UPDATE in the
examples above, but it behaves like a DELETE followed by INSERT instead?
Best,
Michael
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
