On Sat, 11 Dec 2010 12:39:39 +0100, TP <paratribulati...@free.fr> wrote:
>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 Side note: string literals should be quoted with single quotes, so INSERT INTO foo VALUES (1, "bar1"); should be INSERT INTO foo VALUES (1, 'bar1'); http://www.sqlite.org/lang_expr.html#litvalue >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? It is by design. At the bottom of http://www.sqlite.org/lang_createtable.html it says: The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only. This means you have to alias the rowid to be able to refer to it in a foreign key clause. In your case, the foreign key clause does not explicitly refer to a specific column in the parent table, foo. SQLite probably tries to find the primary key of the parent table, but there isn't one. In general it is a bad idea to depend on the implicit existence of rowid. Make it a habit to alias rowid to an explicit integer primary key. It makes your code more portable and more readible. For readibility, I would also explicitly name the column in the foreign key clause. So your example would become: CREATE TABLE foo ( id INTEGER PRIMARY KEY NOT NULL , bar TEXT ); CREATE TABLE fox ( dog TEXT , foo_id INTEGER , CONSTRAINT fk_foo_id FOREIGN KEY ( foo_id ) REFERENCES foo (id) ON UPDATE CASCADE ON DELETE CASCADE ); BEGIN; INSERT INTO foo (id,bar) VALUES (1,'bar1'); INSERT INTO fox (dog,foo_id) VALUES ('dog1',last_insert_rowid() ) COMMIT; -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
