I'm not sure if this is an error on my end or on SQLite's. I'm using 3.7.2.
I'm creating two tables as follows:
PRAGMA foreign_keys=ON;
CREATE TABLE 'users' (
'id' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'name' VARCHAR(64) NOT NULL,
PRIMARY KEY('id', 'type') ON CONFLICT REPLACE
);
CREATE TABLE 'meetings' (
'id' INTEGER PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT,
'userId' INTEGER NOT NULL,
'type' INTEGER NOT NULL,
'password' VARCHAR(64) NOT NULL,
FOREIGN KEY('userId', 'type') REFERENCES users('id', 'type') ON DELETE CASCADE
);
I then insert an entry into each table:
INSERT INTO 'users' VALUES(1,4,'Joe');
INSERT INTO 'meetings' ('userId', 'type', 'password') VALUES(1,4,'blah');
So far, so good. Now if I try to perform an insert which does an update on the
first table like so:
INSERT INTO 'users' VALUES(1,4,'Joe C');
The entry in meetings is now gone. Should ON DELETE CASCADE be picking up an
UPDATE as a DELETE via INSERT INTO from ON CONFLICT REPLACE?
Is there perhaps a better way I should be structuring my tables so I can use an
INSERT or UPDATE style command, yet force DELETEs to cascade to the other table?
Thanks.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
