The example below is s subset of my ical storage database which has
problems with foreign keys. Although I tried a lot I couldn't figure out
why the foreign key doesn't work. I took the example from the
documentation and compared to my two tables, there is no major
difference or I don't see the mistake. The delete statement in the last
line doesn't work. I also tried the lates sqlite version but the error
is the same. I always get "Error: near line 44: FOREIGN KEY constraint
failed". Any help is appreciated.
PRAGMA foreign_keys=1;
BEGIN TRANSACTION;
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname VARCHAR
);
CREATE TABLE track(
trackid INTEGER PRIMARY KEY,
trackname VARCHAR,
trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON
DELETE CASCADE
);
END TRANSACTION;
INSERT INTO artist(artistname) VALUES('Testartist');
INSERT INTO track(trackname,trackartist)
VALUES('Testsong',last_insert_rowid());
DELETE FROM artist;
BEGIN TRANSACTION;
CREATE TABLE ical(
id INTEGER PRIMARY KEY,
oid VARCHAR,
description VARCHAR
);
CREATE TABLE icalentry(
id INTEGER PRIMARY KEY,
calendar INTEGER REFERENCES ical(id) ON UPDATE CASCADE ON DELETE
CASCADE,
uuid VARCHAR
);
END TRANSACTION;
INSERT INTO ical(oid,description) VALUES('123','');
INSERT INTO icalentry(calendar,uuid) VALUES(last_insert_rowid(),'abcd');
DELETE FROM ical;
