I apologize for not providing that initially. Here is the schema for the three tables:
CREATE TABLE [T3] ( [f1] INTEGER NOT NULL CONSTRAINT [fk_T3_T2] REFERENCES [T2]([f1]) ON DELETE CASCADE, [f2] INTEGER NOT NULL, [f3] INTEGER NOT NULL, [f4] INTEGER NOT NULL, [f5] INTEGER NOT NULL, [f6] INTEGER NOT NULL, CONSTRAINT [sqlite_autoindex_T3_1] PRIMARY KEY ([f1], [f2])); CREATE TABLE [T2] ( [f1] INTEGER NOT NULL PRIMARY KEY, [f2] INTEGER NOT NULL CONSTRAINT [fk_T2_T1] REFERENCES [T1](f1]) ON DELETE CASCADE, [f3] TEXT NOT NULL); CREATE TABLE [T1] ( [f1] INTEGER NOT NULL, [f2] NUMERIC NOT NULL, [f3] TEXT NOT NULL); Before discussing this a little more, I have a couple other points to present. First, I've been using a third party tool called SQLite Expert to create the database, and it generated the schema above. Looking at the SQLite web site, I noticed that another way to create FKs was using the FOREIGN KEY clause on the CREATE TABLE command. Trying that on a fresh, blank database did not alleviate what I'm observing. Second, I was initially able to get data into the database without violating any FK constraints because I initially didn't have FK enforcement turned on in my application. I did not realize, or had conveniently forgotten, that FKs are not enforced by default. Turning it on, however, allowed the problems I have been experiencing to manifest. You will notice that T3 contains a compound primary key, but I've tried the DELETE without the compounding primary key (and without any indexes on the table) and still had the same error. Another thing I've come to notice as well is that INSERT statements on T2 do not succeed either, also giving a "foreign key constraint" error that is alleviated when the FK (fk_T2_T1) is removed. I can insert data into T3 when there is data existing in T2 without any issues, even with its FK constraint. Needless to say, this is rather puzzling. Kenneth On 12/31/2010 7:45 PM, BareFeetWare wrote: > Please post your schema instead of describing it. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
