Foreign Key errors

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Foreign Key errors

phaworth
It seems that foreign key errors on columns where the foreign key
definition has a constraint name don't include the constraint name in the
error message.  This is using sqlite version 3.8.3.1.

Is this under the control of a compile switch or PRAGMA or am I stuck with
the way it is?

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

Re: Foreign Key errors

Richard Hipp-3
On Tue, May 6, 2014 at 5:12 PM, Peter Haworth <[hidden email]> wrote:

> It seems that foreign key errors on columns where the foreign key
> definition has a constraint name don't include the constraint name in the
> error message.  This is using sqlite version 3.8.3.1.
>
> Is this under the control of a compile switch or PRAGMA or am I stuck with
> the way it is?
>

SQLite does not keep track of which foreign key constraints fail.  It
simply keeps a counter which is incremented whenever a foreign key
constraint fails and decremented whenever the foreign key constraint is
resolved.  If that counter is positive at the conclusion of a statement,
that means one or more foreign key constraints failed, but it provides no
information about which constraint.

(Actually, there are two counters, one for immediate constraints and
another for deferred constraints.)

It is theoretically possible to keep track of which constraints are failing
so that the particular constraint can be identified in the error message.
But that woudl require more memory and CPU cycles.




>
> Pete
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign Key errors

Petite Abeille-2

On May 6, 2014, at 11:17 PM, Richard Hipp <[hidden email]> wrote:

> It is theoretically possible to keep track of which constraints are failing
> so that the particular constraint can be identified in the error message.
> But that woudl require more memory and CPU cycles.

That would be resources well spent.

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

Re: Foreign Key errors

Stephan Beal-3
On Tue, May 6, 2014 at 11:24 PM, Petite Abeille <[hidden email]>wrote:

> On May 6, 2014, at 11:17 PM, Richard Hipp <[hidden email]> wrote:
>
> > It is theoretically possible to keep track of which constraints are
> failing
> > so that the particular constraint can be identified in the error message.
> > But that woudl require more memory and CPU cycles.
>
> That would be resources well spent.
>

And might even be justifiable given other recent speed improvements which
offset them ;).

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign Key errors

Dominique Devienne
In reply to this post by Richard Hipp-3
On Tue, May 6, 2014 at 11:17 PM, Richard Hipp <[hidden email]> wrote:

> On Tue, May 6, 2014 at 5:12 PM, Peter Haworth <[hidden email]> wrote:
>> It seems that foreign key errors on columns where the foreign key
>> definition has a constraint name don't include the constraint name in the
>> error message.  This is using sqlite version 3.8.3.1.
>>
>> Is this under the control of a compile switch or PRAGMA or am I stuck with
>> the way it is?
>
> [...] It is theoretically possible to keep track of which constraints are failing
> so that the particular constraint can be identified in the error message.
> But that woudl require more memory and CPU cycles.

I agree with Peter, "Petite Abeille", and Stephan, I'd like to see
which FOREIGN KEY constraint(s) fails, similarly to how one sees now
which CHECK constraints fails.

I'd even argue that anyone using FKs wants this behavior by default,
when #pragma foreign_key is on, but yet another pragma to enable
naming which FK failed would be fine too. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign Key errors

phaworth
I'd vote for having this as a future enhancement under the control of a pragma or some other way of making it optional.  Some of my tables have more than 1 foreign key and without the constraint name I have to write application code to pre-check for foreign key errors since I can't translate the error into a user friendly message.

Thanks for considering this.

Pete

Reply | Threaded
Open this post in threaded view
|

Re: Foreign Key errors

Constantine Yannakopoulos
On May 8, 2014 4:39 AM, "phaworth" <[hidden email]> wrote:
>
> I'd vote for having this as a future enhancement under the control of a
> pragma or some other way of making it optional.  Some of my tables have
more
> than 1 foreign key and without the constraint name I have to write
> application code to pre-check for foreign key errors since I can't
translate
> the error into a user friendly message.

Same problem here. When the fk violation occurs inside a large transaction,
e.g. during a bulk data import it is essential that the user is given any
possible help to be able to locate the data that violates ref integrity and
fix it. Without this, prechecking the fks is the only option. And I would
argue in favor of a compiler switch to enable instead of a pragma.

Btw, if you decide to implement it, please format the error messages in
such a way that it will be easy to extract the name of the table/constraint
with code, e.g. with a regexp. For example quote them. Another thought is
to include the name of the last savepoint at the time of the violation in
the error message if there is one. It may be helpful to locate the
offending record if the violated constraint was deferred.

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

Re: Foreign Key errors

Simon Slavin-3

On 8 May 2014, at 9:00am, Constantine Yannakopoulos <[hidden email]> wrote:

> When the fk violation occurs inside a large transaction,
> e.g. during a bulk data import it is essential that the user is given any
> possible help to be able to locate the data that violates ref integrity and
> fix it.

That's your job.  You're the programmer.  SQLite does not talk to users and its result values should never be shown to a user.

> Without this, prechecking the fks is the only option.

No need.  You execute the command as expected.  Only if it returns SQLITE_CONSTRAINT does your software need to start doing complicated things to figure out what caused the problem.

> And I would
> argue in favor of a compiler switch to enable instead of a pragma.

I would argue that consideration of this should go into the design of SQLite4.  I don't think it's worth doing it for SQLite3.

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

Re: Foreign Key errors

Constantine Yannakopoulos
On Thu, May 8, 2014 at 11:50 AM, Simon Slavin <[hidden email]> wrote:

> That's your job.  You're the programmer.  SQLite does not talk to users
> and its result values should never be shown to a user.
>

​Fair enough, but SQLite needs to give me what I need to be able to
communicate something helpful to the user.​ A generic error code and a
"Foreign key constraint failed" error message without any means of getting
some context is not very helpful.


> No need.  You execute the command as expected.  Only if it returns
> SQLITE_CONSTRAINT does your software need to start doing complicated things
> to figure out what caused the problem.
>

​Yes, but what of the constraint is deferred?​
 I get the error​ at COMMIT instead of when the offending DML is executed
​and at that point, when all I have is the error code and error message, it
is impossible to find which statement has caused the violation.​



> I would argue that consideration of this should go into the design of
> SQLite4.  I don't think it's worth doing it for SQLite3.
>

​I beg to differ. having worked with a number of databases, SQLite3 is the
only one that has this defect -forgive the choice of word but I do consider
it as such, and it has caused me lots of trouble from people trying to
import data or trying to implement database replication, myself included.
The only alternatives seem to be to execute DML statements in autocommit
mode (no BEGIN/END), which as you probably guess has dismal performance, or
completely refrain from using deferred constraints, which limits database
design choices. If there is another solution I would be more than happy to
hear it.

​I would be eager to switch to SQLite4 ASAP but it seems very far from even
a beta release.​
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Foreign Key errors

Simon Slavin-3

On 8 May 2014, at 10:40am, Constantine Yannakopoulos <[hidden email]> wrote:

> On Thu, May 8, 2014 at 11:50 AM, Simon Slavin <[hidden email]> wrote:
>
>> That's your job.  You're the programmer.  SQLite does not talk to users
>> and its result values should never be shown to a user.
>
> ​Fair enough, but SQLite needs to give me what I need to be able to
> communicate something helpful to the user.​ A generic error code and a
> "Foreign key constraint failed" error message without any means of getting
> some context is not very helpful.

It's a fair point.  Perhaps a future version of sqlite3_extended_result_codes() can return a string as well as the code, the string being the name of the constraint that was violated.

However my understanding is that including this in SQLite3 would be difficult since its way of figuring out whether there's a constraint violation is limited.  Which is why I suspect this will be in SQLite4, not SQLite3.

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

Re: Foreign Key errors

Constantine Yannakopoulos
On Thu, May 8, 2014 at 1:08 PM, Simon Slavin <[hidden email]> wrote:

> Perhaps a future version of sqlite3_extended_result_codes() can return a
> string as well as the code, the string being the name of the constraint
> that was violated.
>

Yes, it would be great to be able to get the necessary info in a structured
format instead of having to parse the error message​. But I think that most
if not all other databases do not implement this, so I would settle for
parsing the message.

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

Re: Foreign Key errors

Walter Hurry
In reply to this post by Richard Hipp-3
On Thu, 08 May 2014 13:15:54 +0300, Constantine Yannakopoulos wrote:
 most if not all other databases do not implement this

$ psql
psql (9.3.4)
Type "help" for help.

misc=# create table parent
misc-# (a_id varchar(1),
misc(#  constraint pk_parent primary key(a_id));
CREATE TABLE
misc=# create table child
misc-# (b_id varchar(1),
misc(#  a_id varchar(1),
misc(#  constraint fk_child_parent foreign key
misc(#  (a_id) references parent);
CREATE TABLE
misc=# insert into parent values ('a');
INSERT 0 1
misc=# insert into child values ('1','a');
INSERT 0 1
misc=# insert into child values ('2','a');
INSERT 0 1
misc=# insert into child values ('1','b');
ERROR:  insert or update on table "child" violates foreign key constraint
"fk_child_parent"
DETAIL:  Key (a_id)=(b) is not present in table "parent".
misc=#


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

Re: Foreign Key errors

Constantine Yannakopoulos
On Thu, May 8, 2014 at 10:52 PM, Walter Hurry <[hidden email]> wrote:

> On Thu, 08 May 2014 13:15:54 +0300, Constantine Yannakopoulos wrote:
>  most if not all other databases do not implement this
>
> $ psql
> psql (9.3.4)
> Type "help" for help.
> ​<snip>​
>
>
​I meant that they do not implement a way to *directly* get attributes of
the error like table name, constraint name​, record key etc. e.g. by means
of api functions. Or if some do I haven't found the way. All databases
include this information in the error message, and in such a way that it is
easily extractable by text parsing, e.g. using a regexp with groups. And as
you see, postgresql also quotes the names to make parsing easy, which is
what I've asked for. I've used such an approach with both MSSQL and Oracle
with success.

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

Re: Foreign Key errors

phaworth
In reply to this post by Dominique Devienne
After looking into error messages further, I see that the constraint name is not included in NOT NULL or UNIQUE constraint errors although it is there in CHECK constraint errors.  The table.column that caused the error is in the messages so it is possible to identify which column caused the error.  I'm guessing the constraint name is there for CHECK errors because there can be more than one of them for a column so the column name doesn't uniquely identify which check failed.

Since constraint names don't seem to turn up anywhere except CHECK constraint errors, is there any point in defining them?

Pete