Hello, I have a question about referential integrity when there is no explicitly defined primary key in the table on the one side. Look at this example:
-------------------------- PRAGMA foreign_keys = ON; CREATE TABLE foo( bar ); INSERT INTO foo values( "bar1" ); CREATE TABLE fox( dog , foo_id , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE CASCADE ); INSERT INTO fox values( "dog1", 1 ); Error: foreign key mismatch SELECT *, rowid from foo; bar rowid -------------------- -------------------- bar1 1 --------------------------- So, we obtain a "foreign key mismatch", whereas there is the suitable value of rowid in the table. If we modify the previous example by including an explicit primary key in table foo, it works: -------------------------- PRAGMA foreign_keys = ON; CREATE TABLE foo( foo_id INTEGER PRIMARY KEY , bar ); INSERT INTO foo values( 1, "bar1" ); CREATE TABLE fox( dog , foo_id , FOREIGN KEY ( foo_id ) REFERENCES foo ON UPDATE CASCADE ON DELETE CASCADE ); INSERT INTO fox values( "dog1", 1 ); select * from fox; dog1|1 -------------------------- What is the explanation for this behavior? Thanks in advance, Julien _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users