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
