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
