Quantcast

Is this a foreign key bug ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Is this a foreign key bug ?

Domingo Alvarez Duarte
Hello !

I'm getting an error that seems to be a bug in sqlite3 or a change of
behavior.

===

CREATE TABLE a(
     keyval varchar(25)  NOT NULL,
     testid integer  NOT NULL,
     PRIMARY KEY ("keyval","testid")
);

CREATE TABLE b(
     sampleid integer  NOT NULL,
     prodspeckey varchar(25)  NOT NULL,
     PRIMARY KEY ("sampleid"),
     CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey")
REFERENCES "a" ("keyval")
);

PRAGMA foreign_key_check;
===

output

===

sqlite3 < bug-fk.sql
Error: near line 14: foreign key mismatch - "b" referencing "a"

===

Cheers !

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is this a foreign key bug ?

Dan Kennedy-4
On 04/15/2017 07:16 PM, Domingo Alvarez Duarte wrote:

> Hello !
>
> I'm getting an error that seems to be a bug in sqlite3 or a change of
> behavior.
>
> ===
>
> CREATE TABLE a(
>     keyval varchar(25)  NOT NULL,
>     testid integer  NOT NULL,
>     PRIMARY KEY ("keyval","testid")
> );
>
> CREATE TABLE b(
>     sampleid integer  NOT NULL,
>     prodspeckey varchar(25)  NOT NULL,
>     PRIMARY KEY ("sampleid"),
>     CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey")
> REFERENCES "a" ("keyval")
> );
>
> PRAGMA foreign_key_check;
> ===
>
> output
>
> ===
>
> sqlite3 < bug-fk.sql
> Error: near line 14: foreign key mismatch - "b" referencing "a"

It's not a bug. There is no unique constraint on the parent key
"a.keyval". "a.keyval" is part of a PRIMARY KEY, but is not by itself
guaranteed to be unique. Hence the error.

This is the same case as the last example ("CREATE TABLE child7...") in
the first block of code here:

   http://sqlite.org/foreignkeys.html#fk_indexes

Dan.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is this a foreign key bug ?

Domingo Alvarez Duarte
Hello Dan !

Thank you for the reply !

It was my fault of knowledge here, thanks !


On 15/04/17 10:11, Dan Kennedy wrote:

> On 04/15/2017 07:16 PM, Domingo Alvarez Duarte wrote:
>> Hello !
>>
>> I'm getting an error that seems to be a bug in sqlite3 or a change of
>> behavior.
>>
>> ===
>>
>> CREATE TABLE a(
>>     keyval varchar(25)  NOT NULL,
>>     testid integer  NOT NULL,
>>     PRIMARY KEY ("keyval","testid")
>> );
>>
>> CREATE TABLE b(
>>     sampleid integer  NOT NULL,
>>     prodspeckey varchar(25)  NOT NULL,
>>     PRIMARY KEY ("sampleid"),
>>     CONSTRAINT "qasamples_ibfk_1" FOREIGN KEY ("prodspeckey")
>> REFERENCES "a" ("keyval")
>> );
>>
>> PRAGMA foreign_key_check;
>> ===
>>
>> output
>>
>> ===
>>
>> sqlite3 < bug-fk.sql
>> Error: near line 14: foreign key mismatch - "b" referencing "a"
>
> It's not a bug. There is no unique constraint on the parent key
> "a.keyval". "a.keyval" is part of a PRIMARY KEY, but is not by itself
> guaranteed to be unique. Hence the error.
>
> This is the same case as the last example ("CREATE TABLE child7...")
> in the first block of code here:
>
>   http://sqlite.org/foreignkeys.html#fk_indexes
>
> Dan.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...