Make it your rule of thumb: don't ever use rowid, declare your own column as "integer primary key" and use it. It will come at no cost for you and everything else will work much better. Here is simplified example of your problem and solution:
sqlite> pragma foreign_keys=on; sqlite> create table t (n int); sqlite> create table tt (n int); sqlite> create table ttt (a int, b int, foreign key (a) references t (rowid), foreign key (b) references tt (rowid)); sqlite> insert into t values (1); sqlite> insert into tt values (1); sqlite> insert into ttt values (1, 1); Error: foreign key mismatch sqlite> drop table t; sqlite> drop table tt; sqlite> drop table ttt; sqlite> create table t (id integer primary key, n int); sqlite> create table tt (id integer primary key, n int); sqlite> create table ttt (a int, b int, foreign key (a) references t (id), foreign key (b) references tt (id)); sqlite> insert into t (n) values (1); sqlite> insert into tt (n) values (1); sqlite> insert into ttt values (1, 1); sqlite> delete from t; Error: foreign key constraint failed sqlite> Pavel On Mon, Aug 23, 2010 at 9:23 AM, inst <i...@mail.ua> wrote: > Thanks for your answer, Oliver. > >> http://www.sqlite.org/foreignkeys.html > Yes, I have already read this before mailing here. > >> PRAGMA foreign_keys > Yes, I knew about this PRAGMA. > > Ok, I'll try to describe my problem with one example. Imagine we have > to store information about pages and the keywords. Any page may > contain any number of keywords, but any keyword may also belong to any > number of pages. So I'm trying to implement many-to-many relationship. > > Here is small copy and paste from my terminal: > > SQLite version 3.7.0.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> PRAGMA foreign_keys = ON; > sqlite> CREATE TABLE pages ( > ...> address VARCHAR ( 128 ) DEFAULT '/', > ...> body TEXT DEFAULT '' > ...> ); > sqlite> CREATE TABLE keywords ( word VARCHAR ( 64 ) NOT NULL UNIQUE ); > sqlite> CREATE TABLE relations ( > ...> page INTEGER NOT NULL, > ...> keyword INTEGER NOT NULL, > ...> FOREIGN KEY ( page ) REFERENCES pages( rowid ) ON UPDATE > CASCADE ON DELETE CASCADE, > ...> FOREIGN KEY ( keyword ) REFERENCES keywords( rowid ) ON UPDATE > CASCADE ON DELETE CASCADE > ...> ); > sqlite> INSERT INTO pages ( body ) VALUES ( '' ); > sqlite> INSERT INTO pages ( address ) VALUES ( '/contacts' ); > sqlite> INSERT INTO keywords ( word ) VALUES ( 'word1' ); > sqlite> INSERT INTO keywords ( word ) VALUES ( 'word2' ); > > As for this place all were ok and here is the problem begin: > > sqlite> INSERT INTO relations VALUES ( 1,2 ); > Error: foreign key mismatch > sqlite> INSERT INTO relations VALUES ( 1,1 ); > Error: foreign key mismatch > > Can anyone please tell what I did wrong? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
