On Sun, Nov 11, 2012 at 11:16 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 12 Nov 2012, at 5:49am, Darren Spruell <phatbuck...@gmail.com> 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