Re: [sqlite] Confusion on 'foreign key mismatch' errors

2012-11-12 Thread Simon Slavin 

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

2012-11-11 Thread Darren Spruell 
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

2012-11-11 Thread Simon Slavin 

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

2012-11-11 Thread Pavel Ivanov 
> # 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

[sqlite] Confusion on 'foreign key mismatch' errors

2012-11-11 Thread Darren Spruell 
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