C API reference manpages

classic Classic list List threaded Threaded
41 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Re: Difference between sqlite3 executable and application

Simon Slavin-3

On 4 Apr 2016, at 12:05pm, Domingo Alvarez Duarte <[hidden email]> wrote:

> Thanks for reply !  
>
> If I issue this command :  
>
> PRAGMA case_sensitive_like = OFF;  
>
> Then I get the same answer (I mean it uses indexes now):  
>
> explain query plan select * from companies where post_code like 'rg%';  
>
> SEARCH TABLE companies USING INDEX companies_post_code_idx (post_code>? AND
> post_code<?)  
>
> It seems strange why sqlite is not using the indexes with the default
> case_sensitive_like settings, here is the schema:  

Well I'm glad I helped you fix the problem.  Unfortunately I do not know where SQLite gets its default setting for "PRAGMA case_sensitive_like".  The situation you reported does look rather strange.  Maybe someone else can help.

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

FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
Hello !  

I know this was discussed here before several times, but it seems that still
we have poor solution for this on sqlite:  

FOREIGN KEY constraint failed  

I have some databases with lots of foreign keys and I sometimes I need to
change the structure of some tables and I get this message, it's better than
nothing but sqlite knows which table/field failed but do not tell us making
it hard on some databases to find the problem.  

Can this message also include the offending table/key ?  

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
|

Re: FOREIGN KEY constraint failed

Richard Hipp-3
On 4/4/16, Domingo Alvarez Duarte <[hidden email]> wrote:
> sqlite knows which table/field failed

No it doesn't, actually.  SQLite keeps a counter of FK violations and
resolutions, and if that counter is zero at the the end of the
transaction, then it knows that no FK constraints have failed.  If the
counter is non-zero, then it knows that one or more FK constraints
have failed, but it has no idea which ones.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
Fair enough !  

But even then could it have let's say a place for record the last foreign key
violation ?  

Only one place will be better than nothing, it can be overwritten every time
a foreign key is found and at least we could have a message like this:  

"FOREIGN KEY constraint failed (x : last = (offending table, offending field)
|| (constraint name))"  

Where "x" is the number of violations and the last violation recorded, this
way at least we can start solving then one by one (probably in most cases
will be only one).  

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
|

Re: FOREIGN KEY constraint failed

Clemens Ladisch
Domingo Alvarez Duarte wrote:
> But even then could it have let's say a place for record the last foreign key
> violation ?

This would not help if that last constraint is no longer violated at the
end of the transaction.


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

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
Thank you for reply !  

Good point, but for sure there is great minds here that can come with a
solution to this problem !  

Sqlite could use a table in memory (or a hash table) and add delete from it
as it found violations/resolve then and at then end it has all the violations
to show, it can be done lazy so if no violations occur no hash table is ever
created.  


The actual situation is better than nothing but it's still frustrating !  

Cheers !  
>  Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  Domingo Alvarez Duarte wrote:
>  
>>But even then could it have let's say a place for record the last foreign
>>key
>> violation ?
>>

>  This would not help if that last constraint is no longer violated at the
> end of the transaction.
>
>
> Regards,
> Clemens
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
I think I found a solution to it !  

If the cost of it is too much for normal operation we could have a "pragma
debug_foreign_key" (or debug everything) and turn it on, then run again the
failed statement and in this mode extra code that saves all info for a
detailed error message can be run without problem.  

   

>  Mon Apr 04 2016 07:49:57 PM CEST from "Domingo Alvarez Duarte"
><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint
>failed
>
>  Thank you for reply !
>
> Good point, but for sure there is great minds here that can come with a
> solution to this problem !
>
> Sqlite could use a table in memory (or a hash table) and add delete from it
> as it found violations/resolve then and at then end it has all the
>violations
> to show, it can be done lazy so if no violations occur no hash table is
>ever
> created.
>
>
> The actual situation is better than nothing but it's still frustrating !
>
> Cheers !
>  
>>Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
>> <[hidden email]> Subject: Re: [sqlite] FOREIGN KEY constraint failed
>>
>> Domingo Alvarez Duarte wrote:
>>
>>  
>>>But even then could it have let's say a place for record the last foreign
>>> key
>>> violation ?
>>>
>>>

>>  This would not help if that last constraint is no longer violated at the
>> end of the transaction.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> 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
>
>
>  

 

 

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

Re: FOREIGN KEY constraint failed

Stephen Chrzanowski
In reply to this post by Domingo Alvarez Duarte-2
I've never developed for a platform with a small memory footprint (Unless
you talk about the Vic20 as being the smallest footprint I've used, but not
relevant to SQLite, and my skill was writing text based paint program),
but, throwing results like this into memory could cause more issues for
devices that have KILOBYTES worth of memory to play with as a whole, versus
a machine with Gigabytes of extra-never-been-used-in-its-lifespan memory.

If maybe there were some kind of interface that the library could expose so
that if a constraint error occurs, something external to SQLite can deal
with it, regardless of what the results are.  If the developer wants to
know what the constraint faults are, they just map their own routines to
whatever interface SQLite could provide and the SQLite engine can just feed
whatever it can.  The owness of whatever that output is belongs to the
developer, not to the SQLite dev team, and reservations on what the output
of this new routine is apt to change at any time.  If the events triggered
by the constraint failures are invalid at the start, but then become valid
later on, it'd be again up to the developer to determine what happened, not
the SQLite dev team.

My two cents.


On Mon, Apr 4, 2016 at 1:49 PM, Domingo Alvarez Duarte <
[hidden email]> wrote:

> Thank you for reply !
>
> Good point, but for sure there is great minds here that can come with a
> solution to this problem !
>
> Sqlite could use a table in memory (or a hash table) and add delete from it
> as it found violations/resolve then and at then end it has all the
> violations
> to show, it can be done lazy so if no violations occur no hash table is
> ever
> created.
>
>
> The actual situation is better than nothing but it's still frustrating !
>
> Cheers !
> >  Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
> ><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
> >
> >  Domingo Alvarez Duarte wrote:
> >
> >>But even then could it have let's say a place for record the last foreign
> >>key
> >> violation ?
> >>
>
> >  This would not help if that last constraint is no longer violated at the
> > end of the transaction.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > 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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
Thanks for reply !  

Thank you for your solution I think it improves the actual situation !  

Cheers !  

>  Mon Apr 04 2016 08:00:38 PM CEST from "Stephen Chrzanowski"
><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  I've never developed for a platform with a small memory footprint (Unless
> you talk about the Vic20 as being the smallest footprint I've used, but not
> relevant to SQLite, and my skill was writing text based paint program),
> but, throwing results like this into memory could cause more issues for
> devices that have KILOBYTES worth of memory to play with as a whole, versus
> a machine with Gigabytes of extra-never-been-used-in-its-lifespan memory.
>
> If maybe there were some kind of interface that the library could expose so
> that if a constraint error occurs, something external to SQLite can deal
> with it, regardless of what the results are. If the developer wants to
> know what the constraint faults are, they just map their own routines to
> whatever interface SQLite could provide and the SQLite engine can just feed
> whatever it can. The owness of whatever that output is belongs to the
> developer, not to the SQLite dev team, and reservations on what the output
> of this new routine is apt to change at any time. If the events triggered
> by the constraint failures are invalid at the start, but then become valid
> later on, it'd be again up to the developer to determine what happened, not
> the SQLite dev team.
>
> My two cents.
>
>
> On Mon, Apr 4, 2016 at 1:49 PM, Domingo Alvarez Duarte <
> [hidden email]> wrote:
>
>  
>>Thank you for reply !
>>
>> Good point, but for sure there is great minds here that can come with a
>> solution to this problem !
>>
>> Sqlite could use a table in memory (or a hash table) and add delete from
>>it
>> as it found violations/resolve then and at then end it has all the
>> violations
>> to show, it can be done lazy so if no violations occur no hash table is
>> ever
>> created.
>>
>>
>> The actual situation is better than nothing but it's still frustrating !
>>
>> Cheers !
>>  
>>>Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
>>> <[hidden email]> Subject: Re: [sqlite] FOREIGN KEY constraint failed
>>>
>>> Domingo Alvarez Duarte wrote:
>>>
>>>  
>>>>But even then could it have let's say a place for record the last foreign
>>>> key
>>>> violation ?
>>>>
>>>>

>>>  This would not help if that last constraint is no longer violated at the
>>> end of the transaction.
>>>
>>>
>>> Regards,
>>> Clemens
>>> _______________________________________________
>>> 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
>>

>  _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Tim Streater-3
In reply to this post by Domingo Alvarez Duarte-2
On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte <[hidden email]> wrote:

> Fair enough !
>
> But even then could it have let's say a place for record the last foreign key
> violation ?
>
> Only one place will be better than nothing, it can be overwritten every time
> a foreign key is found and at least we could have a message like this:

You can write your own wrapper to do this. I have 850 places in my app where I do query or exec, so in the event of failure I need to know which one and what the sql was. When a problem occurs I log this information.

--
Cheers  --  Tim

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

Re: FOREIGN KEY constraint failed

Petite Abeille-2
In reply to this post by Richard Hipp-3

> On Apr 4, 2016, at 6:14 PM, Richard Hipp <[hidden email]> wrote:
>
> On 4/4/16, Domingo Alvarez Duarte <[hidden email]> wrote:
>> sqlite knows which table/field failed
>
> No it doesn't, actually.

And yet, that same question comes over, and over, and over, ad nauseam. Each and every time a poor soul is confronted with that obscure message. Sigh.

Oh well…

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

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
In reply to this post by Domingo Alvarez Duarte-2
Thanks for reply !  

I also do it using the sqlite3_trace hook but when you have a database with a
bunch of foreign keys it would be a lot easier/faster if sqlite could tell us
exactly where it was (sqlite must know it to flag the error).  

I already found the problem but it took 3 hours to find.  

Cheers !  

>  Mon Apr 04 2016 10:17:00 PM CEST from "Tim Streater"
><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte
><[hidden email]> wrote:
>
>  
>>Fair enough !
>>
>> But even then could it have let's say a place for record the last foreign
>>key
>> violation ?
>>
>> Only one place will be better than nothing, it can be overwritten every
>>time
>> a foreign key is found and at least we could have a message like this:
>>

>  You can write your own wrapper to do this. I have 850 places in my app
>where I do query or exec, so in the event of failure I need to know which one
>and what the sql was. When a problem occurs I log this information.
>
> --
> Cheers -- Tim
>
>   (, 0 bytes) [View| Download]
>   
>
>  
>
>  
>
>  
 

 


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

(262 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
In reply to this post by Petite Abeille-2
Thanks for reply !  

I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" and
when set throw any kind of error to stderr/sqlite3_(hook) this way ther is no
need to store temporary conditions to show later.  

And of course sqlite knows which table/field failed to flag the error, it
doesn't throw a dice to do it.
 

Cheers !  

>  Mon Apr 04 2016 10:35:35 PM CEST from "Petite Abeille"
><[hidden email]>  Subject: Re: [sqlite] FOREIGN KEY constraint
>failed
>
>    
>>On Apr 4, 2016, at 6:14 PM, Richard Hipp <drh@sqliteorg> wrote:
>>
>> On 4/4/16, Domingo Alvarez Duarte <[hidden email]> wrote:
>>  
>>>sqlite knows which table/field failed
>>>

>>  No it doesn't, actually.
>>

>  And yet, that same question comes over, and over, and over, ad nauseam.
>Each and every time a poor soul is confronted with that obscure message.
>Sigh.
>
> Oh well…
>
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

KlaasV
In reply to this post by Domingo Alvarez Duarte-2
On Mon, 04 Apr 2016 17:51:30 +0200 Domingo Alvarez Duarte wrote:

>FOREIGN KEY constraint failed  

>I have some databases with lots of foreign keys and I sometimes I need to
>change the structure of some tables and I get this message

Before restructuring a database schema always use:

PRAGMA foreign_keys = OFF;

After restructuring is executed successfully you can test the result by querying with this pragma ON.
http://sqlite.org/pragma.html#pragma_foreign_keys

Kind regards/Vriendelijke groeten.
Klaas `Z4us` Van B., CEO/CIO LI#437429414
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Klaas "Z4us" V, MetaDBA at InnocentIsArt.EU
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

R Smith
In reply to this post by Domingo Alvarez Duarte-2


On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote:
> Thanks for reply !
>
> I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" and
> when set throw any kind of error to stderr/sqlite3_(hook) this way ther is no
> need to store temporary conditions to show later.
>
> And of course sqlite knows which table/field failed to flag the error, it
> doesn't throw a dice to do it.

The thing you are missing, is that there might be thousands of FK
violations throughout a transaction, all of which (or most of which)
might get resolved before the end of the transaction, and as such is
absolutely useless to inspect/record/notify/whatever.

Let's assume there are one thousand violations, and three of them did
not get resolved, such as violation number 322, no. 567 and no. 828.
If you "ask the user" or the program via API about every one of the 1000
violations, surely the time-waste will be intense, and even if you can
live with the time-waste, how will the application/user ever know that
violation no. 435, for instance, is going to definitely get resolved so
that it might report back to the API some form of "OK, we can accept
this one" or record for its own purposes the violation to "deal with
later" when in fact at some point it gets resolved without necessarily a
second check and certainly not a second failure to revisit it?

It is extremely rare that the "Last violation" (number 1000 in our
example above) is going to end up being THE ONE, or even "one of" the
offenders.  Perhaps only in cases where there is only 1 FK violation in
the entire scope of the transaction, and those cases are rarest (such as
a single insert/delete) and if it does happen, you already know the
exact item causing violation, so the API to disclose its identity is
superfluous.

The only way this feature is feasible is keeping a complete list of
violations internally and a mechanism to revisit them marking the
resolve (if any). The mechanism itself will be heavy and the memory
footprint of the list can run into gigabytes easily, even for a mediocre
database, seeing as a transaction updating 10 rows may easily need to
check hundreds of FK constraints and recursed constraints.

As Mr. Bee pointed out - we see this question asked often, lots of
people would like to have it implemented. This may be true, but that's
simply because, without investigation, the concept/implementation seems
easy to lots of people. It only seems that way though.

Cheers,
Ryan

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

Re: FOREIGN KEY constraint failed

Domingo Alvarez Duarte-2
Thank you for reply !  

Yes you are right in most of your points.  

The code overhead/footprint can be inside preprocessor macros and we can have
for development as we normally do a build with debug ON so in production
there will be nothing new.  

Throwing a bunch of false positives to stderr/sqlite3_(hook*) in deep debug
mode can be managed by a user script that go through the output and filter
then or do other things each use case my require.  

For example when I asked this question again I've got this error in a
"delete" statement with one specific id but that exploded to several possible
tables/fields combination and it took me 3 hours to find it. As usual it was
silly once you discover it but till then you loose some hairs.  


Let's have a compile time debug mode in sqlite where things like this and
others can make possible find alternative ways to solve everyday problems, (I
tried to find a way to do it myself through the sqlite3 sources but I
couldn't find my way through the virtual machine with the available
documentation).  

Cheers !  

>  Tue Apr 05 2016 12:58:07 PM CEST from "R Smith" <[hidden email]>
>Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote:
>  
>>Thanks for reply !
>>
>> I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE"
>>and
>> when set throw any kind of error to stderr/sqlite3_(hook) this way ther is
>>no
>> need to store temporary conditions to show later.
>>
>> And of course sqlite knows which table/field failed to flag the error, it
>> doesn't throw a dice to do it.
>>

>  The thing you are missing, is that there might be thousands of FK
> violations throughout a transaction, all of which (or most of which)
> might get resolved before the end of the transaction, and as such is
> absolutely useless to inspect/record/notify/whatever.
>
> Let's assume there are one thousand violations, and three of them did
> not get resolved, such as violation number 322, no. 567 and no. 828.
> If you "ask the user" or the program via API about every one of the 1000
> violations, surely the time-waste will be intense, and even if you can
> live with the time-waste, how will the application/user ever know that
> violation no. 435, for instance, is going to definitely get resolved so
> that it might report back to the API some form of "OK, we can accept
> this one" or record for its own purposes the violation to "deal with
> later" when in fact at some point it gets resolved without necessarily a
> second check and certainly not a second failure to revisit it?
>
> It is extremely rare that the "Last violation" (number 1000 in our
> example above) is going to end up being THE ONE, or even "one of" the
> offenders. Perhaps only in cases where there is only 1 FK violation in
> the entire scope of the transaction, and those cases are rarest (such as
> a single insert/delete) and if it does happen, you already know the
> exact item causing violation, so the API to disclose its identity is
> superfluous.
>
> The only way this feature is feasible is keeping a complete list of
> violations internally and a mechanism to revisit them marking the
> resolve (if any). The mechanism itself will be heavy and the memory
> footprint of the list can run into gigabytes easily, even for a mediocre
> database, seeing as a transaction updating 10 rows may easily need to
> check hundreds of FK constraints and recursed constraints.
>
> As Mr. Bee pointed out - we see this question asked often, lots of
> people would like to have it implemented. This may be true, but that's
> simply because, without investigation, the concept/implementation seems
> easy to lots of people. It only seems that way though.
>
> Cheers,
> Ryan
>
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

Keith Medcalf
In reply to this post by R Smith

Are we confusing immediate constraints (checked per statement) with DEFERRED constraints (checked at COMMIT time) again?

> -----Original Message-----
> From: [hidden email] [mailto:sqlite-users-
> [hidden email]] On Behalf Of R Smith
> Sent: Tuesday, 5 April, 2016 06:58
> To: [hidden email]
> Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>
>
> On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote:
> > Thanks for reply !
> >
> > I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE"
> and
> > when set throw any kind of error to stderr/sqlite3_(hook) this way ther
> is no
> > need to store temporary conditions to show later.
> >
> > And of course sqlite knows which table/field failed to flag the error,
> it
> > doesn't throw a dice to do it.
>
> The thing you are missing, is that there might be thousands of FK
> violations throughout a transaction, all of which (or most of which)
> might get resolved before the end of the transaction, and as such is
> absolutely useless to inspect/record/notify/whatever.
>
> Let's assume there are one thousand violations, and three of them did
> not get resolved, such as violation number 322, no. 567 and no. 828.
> If you "ask the user" or the program via API about every one of the 1000
> violations, surely the time-waste will be intense, and even if you can
> live with the time-waste, how will the application/user ever know that
> violation no. 435, for instance, is going to definitely get resolved so
> that it might report back to the API some form of "OK, we can accept
> this one" or record for its own purposes the violation to "deal with
> later" when in fact at some point it gets resolved without necessarily a
> second check and certainly not a second failure to revisit it?
>
> It is extremely rare that the "Last violation" (number 1000 in our
> example above) is going to end up being THE ONE, or even "one of" the
> offenders.  Perhaps only in cases where there is only 1 FK violation in
> the entire scope of the transaction, and those cases are rarest (such as
> a single insert/delete) and if it does happen, you already know the
> exact item causing violation, so the API to disclose its identity is
> superfluous.
>
> The only way this feature is feasible is keeping a complete list of
> violations internally and a mechanism to revisit them marking the
> resolve (if any). The mechanism itself will be heavy and the memory
> footprint of the list can run into gigabytes easily, even for a mediocre
> database, seeing as a transaction updating 10 rows may easily need to
> check hundreds of FK constraints and recursed constraints.
>
> As Mr. Bee pointed out - we see this question asked often, lots of
> people would like to have it implemented. This may be true, but that's
> simply because, without investigation, the concept/implementation seems
> easy to lots of people. It only seems that way though.
>
> Cheers,
> Ryan
>
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint failed

R Smith


On 2016/04/05 11:15 PM, Keith Medcalf wrote:
> Are we confusing immediate constraints (checked per statement) with DEFERRED constraints (checked at COMMIT time) again?
>

We might be - though I assume the OP implicated only deferred
constraints - since immediate constraints will fail on contact, and as
such, no mystery surrounds their origins.
My assumption might be wrong.

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

Re: FOREIGN KEY constraint failed

nomad
On Tue Apr 05, 2016 at 11:56:53PM +0200, R Smith wrote:

>
> On 2016/04/05 11:15 PM, Keith Medcalf wrote:
> >Are we confusing immediate constraints (checked per statement) with
> >DEFERRED constraints (checked at COMMIT time) again?
> >
>
> We might be - though I assume the OP implicated only deferred
> constraints - since immediate constraints will fail on contact, and
> as such, no mystery surrounds their origins.  My assumption might be
> wrong.

There is plenty of mystery around immediate constraints when triggers
are involved. I often have an issue with statements failing with the
generic foreign key message where the actual issue is three or four
trigger levels deep. So I can only add my +1 to the "this is an issue"
position as well as a +1 to "please can we have *some* kind of help."

What I usually end up doing is re-executing the statement with foreign
keys turned off, and then run the foreign_key_check pragma. But it
doesn't always work because if the problem trigger/statement doesn't
fail then later statements sometimes mask the original problem.

So my suggestion would be a development pragma to request that SQLite
does this itself before the transaction gets rolled back, adding the
results of the foreign_key_check to the error message.

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

Re: FOREIGN KEY constraint failed

James K. Lowden
In reply to this post by R Smith
On Tue, 5 Apr 2016 23:56:53 +0200
R Smith <[hidden email]> wrote:

> On 2016/04/05 11:15 PM, Keith Medcalf wrote:
> > Are we confusing immediate constraints (checked per statement) with
> > DEFERRED constraints (checked at COMMIT time) again?

In SQLite some constraints are checked per row, not per statement.  

> We might be - though I assume the OP implicated only deferred
> constraints - since immediate constraints will fail on contact, and
> as such, no mystery surrounds their origins.

One table may have more than one constraint.  Primary key violations
are reported as such, but CHECK constraint and FK constraint messages
don't mention the column involved.  

OP: I investigated the problem a year ago or more and concluded it's not
easy to remedy.  Today violations are simply counted.  To report them
in detail would require carrying much more state, and externalizing
constraint conditions in human-readable form.  Unless you have a patch
that does all that, I doubt PRAGMA DEBUG_MODE will be realized.  

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