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