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

Reply via email to