Re: [sqlite] Confusion on 'foreign key mismatch' errors
On 12 Nov 2012, at 7:54am, Darren Spruell wrote: > If I'm not mistaken, that's a problem too, as type definitions are > ignored on FTS columns: Oh. You're trying to mess with a table used in an FTS definition. Okay. Don't do that unless you understand how FTS works. By all means define foreign keys on your own tables in the same database as you're using FTS, but don't do it on ones you're using FTS for. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
On Sun, Nov 11, 2012 at 11:16 PM, Simon Slavin wrote: > > On 12 Nov 2012, at 5:49am, Darren Spruell wrote: > >> sqlite> CREATE TABLE ip ( >> ...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE, >> ...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL, >> ...> FOREIGN KEY(comment) REFERENCES comment); >> sqlite> CREATE INDEX ipcommentindex ON ip(comment); >> sqlite> INSERT INTO comment VALUES ('this is a comment.'); >> sqlite> SELECT rowid,content FROM comment; >> 1|this is a comment. >> sqlite> SELECT last_insert_rowid(); >> 1 >> sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid()); >> Error: foreign key mismatch > > You slipped up in the FOREIGN KEY definition. You're relating it to the text > field of the comment table. You should be relating it to the 'rowid' field, > possibly something like. > > ...> FOREIGN KEY(comment) REFERENCES comment(rowid)); > > However, you cannot relate to rowid, because it's not a properly defined > field. So define an 'id' field for the comment table, then make sure you > refer to it in your FOREIGN KEY definition. If I'm not mistaken, that's a problem too, as type definitions are ignored on FTS columns: # http://www.sqlite.org/fts3.html If column names are explicitly provided for the FTS table as part of the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally specified for each column. This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way. I would want my 'id' field to behave as an autoincrementing integer field (i.e. INTEGER PRIMARY KEY) to work properly. So I think I might be at an impasse with this plan. Maybe I'll drop the foreign key and restructure around it. Thx! -- Darren Spruell phatbuck...@gmail.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
On 12 Nov 2012, at 5:49am, Darren Spruell wrote: > sqlite> CREATE TABLE ip ( > ...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE, > ...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL, > ...> FOREIGN KEY(comment) REFERENCES comment); > sqlite> CREATE INDEX ipcommentindex ON ip(comment); > sqlite> INSERT INTO comment VALUES ('this is a comment.'); > sqlite> SELECT rowid,content FROM comment; > 1|this is a comment. > sqlite> SELECT last_insert_rowid(); > 1 > sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid()); > Error: foreign key mismatch You slipped up in the FOREIGN KEY definition. You're relating it to the text field of the comment table. You should be relating it to the 'rowid' field, possibly something like. ...> FOREIGN KEY(comment) REFERENCES comment(rowid)); However, you cannot relate to rowid, because it's not a properly defined field. So define an 'id' field for the comment table, then make sure you refer to it in your FOREIGN KEY definition. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confusion on 'foreign key mismatch' errors
> # http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/ > Unlike traditional tables, the ROWID of an FTS table is stable through > a vacuum (VACUUM in Appendix C), so it can be reliably referenced > through a foreign key. I'm not sure who wrote that but this page http://www.sqlite.org/lang_createtable.html states explicitly (at the very end): "The parent key of a foreign key constraint is not allowed to use the rowid". Pavel On Sun, Nov 11, 2012 at 9:49 PM, Darren Spruell wrote: > > I'm stuck on some errors related to my use of foreign key constraints > in my application. The following illustrates: > > $ sqlite3 > SQLite version 3.7.9 --SOURCE-ID-- > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> PRAGMA foreign_keys = ON; > sqlite> CREATE VIRTUAL TABLE comment USING fts4(); > sqlite> CREATE TABLE ip ( >...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE, >...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL, >...> FOREIGN KEY(comment) REFERENCES comment); > sqlite> CREATE INDEX ipcommentindex ON ip(comment); > sqlite> INSERT INTO comment VALUES ('this is a comment.'); > sqlite> SELECT rowid,content FROM comment; > 1|this is a comment. > sqlite> SELECT last_insert_rowid(); > 1 > sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid()); > Error: foreign key mismatch > sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',1); > Error: foreign key mismatch > > > Thinking I understand the requirements for foreign key constraints, I > don't know why my INSERT into ip table returns 'foreign key mismatch' > error. > > When I leave foreign key constraints off, things work as I would expect: > > > sqlite> CREATE VIRTUAL TABLE comment USING fts4(); > sqlite> CREATE TABLE ip ( >...> id INTEGER PRIMARY KEY, date_added DEFAULT CURRENT_DATE, >...> ip VARCHAR(15) NOT NULL UNIQUE, comment INTEGER NOT NULL, >...> FOREIGN KEY(comment) REFERENCES comment); > sqlite> CREATE INDEX ipcommentindex ON ip(comment); > sqlite> INSERT INTO comment VALUES ('this is a comment.'); > sqlite> SELECT rowid,content FROM comment; > 1|this is a comment. > sqlite> SELECT last_insert_rowid(); > 1 > sqlite> INSERT INTO ip (ip,comment) VALUES ('10.0.1.1',last_insert_rowid()); > sqlite> SELECT * FROM ip; > 1|2012-11-12|10.0.1.1|1 > > > I suspect the issue might stem from trying to use the rowid in the > comment table as the parent key in the foreign key on the ip table, > reading: > > # http://www.sqlite.org/foreignkeys.html > The parent key is the column or set of columns in the parent table > that the foreign key constraint refers to. This is normally, but not > always, the primary key of the parent table. The parent key must be a > named column or columns in the parent table, not the rowid. > > ...but then this kind of reads like it's supported: > > # http://answers.oreilly.com/topic/1955-how-to-use-full-text-search-in-sqlite/ > Unlike traditional tables, the ROWID of an FTS table is stable through > a vacuum (VACUUM in Appendix C), so it can be reliably referenced > through a foreign key. > > > I'm struggling to find a clear way to achieve a foreign key > constrained relation between these tables if a.) the FTS table can't > define an INTEGER PRIMARY KEY column to function as the parent key for > a child table, or b.) child tables can't reference the rowid on an FTS > parent table as the parent key. > > Clue bat appreciated. > > -- > Darren Spruell > phatbuck...@gmail.com > ___ > 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