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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
