No way to check for CHECK constraint violations a posteriori

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

No way to check for CHECK constraint violations a posteriori

Dominique Devienne
Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
checks CHECK constraints.

Given that there's {{pragma ignore_check_constraints = 1}} which allows to
bypass CHECK constraints, that seems like a surprising oversight.

I even tried {{pragma ignore_check_constraints = 0}} in case it was
re-validate CHECK constraint on that mode change, but apparently not.

Could we please have a new {{pragma check_constraints_check}} pragma,
or alternatively have {{pragma integrity_check}} actually check CHECK
constraint?

Thanks, --DD

PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma
foreign_check_check}} differ in behavior, one return ok, the other nothing.
Ideally they'd be consistent.

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (v CHECK (typeof(v) = 'integer'));
sqlite> insert into t values (1);
sqlite> insert into t values ('two');
Error: CHECK constraint failed: t
sqlite> pragma ignore_check_constraints = 1;
sqlite> insert into t values ('two');
sqlite> pragma integrity_check;
ok
sqlite> pragma foreign_key_check;
sqlite> pragma ignore_check_constraints = 0;
sqlite> pragma quick_check;
ok
_______________________________________________
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: No way to check for CHECK constraint violations a posteriori

Hick Gunter
A CHECK constraint is just a special trigger that allows you to raise an error and only runs on INSERT and UPDATE.

It has nothing to do with internal database structure (pragma integrity_check) or with foreign keys (pragma foreign_key_check; you have to enable foreign key checking first anyway).

If you are disabling CHECK constraints for a specific reason, what causes that reason to go away?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Mittwoch, 22. Februar 2017 14:04
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [sqlite] No way to check for CHECK constraint violations a posteriori

Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}} checks CHECK constraints.

Given that there's {{pragma ignore_check_constraints = 1}} which allows to bypass CHECK constraints, that seems like a surprising oversight.

I even tried {{pragma ignore_check_constraints = 0}} in case it was re-validate CHECK constraint on that mode change, but apparently not.

Could we please have a new {{pragma check_constraints_check}} pragma, or alternatively have {{pragma integrity_check}} actually check CHECK constraint?

Thanks, --DD

PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma foreign_check_check}} differ in behavior, one return ok, the other nothing.
Ideally they'd be consistent.

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t (v CHECK (typeof(v) = 'integer')); insert into t
sqlite> values (1); insert into t values ('two');
Error: CHECK constraint failed: t
sqlite> pragma ignore_check_constraints = 1; insert into t values
sqlite> ('two'); pragma integrity_check;
ok
sqlite> pragma foreign_key_check;
sqlite> pragma ignore_check_constraints = 0; pragma quick_check;
ok
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: No way to check for CHECK constraint violations a posteriori

Richard Hipp-3
In reply to this post by Dominique Devienne
On 2/22/17, Dominique Devienne <[hidden email]> wrote:
> Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
> checks CHECK constraints.

That is now fixed on a branch.  I am still testing the changes prior
to merging onto trunk.

--
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: No way to check for CHECK constraint violations a posteriori

Simon Slavin-3

On 22 Feb 2017, at 3:47pm, Richard Hipp <[hidden email]> wrote:

> On 2/22/17, Dominique Devienne <[hidden email]> wrote:
>> Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
>> checks CHECK constraints.
>
> That is now fixed on a branch.  I am still testing the changes prior
> to merging onto trunk.

Just wanted to point out the PS on DD's original post and ask whether you’d noticed it.

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
|

Re: No way to check for CHECK constraint violations a posteriori

Richard Hipp-3
In reply to this post by Dominique Devienne
On 2/22/17, Dominique Devienne <[hidden email]> wrote:
>
> PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma
> foreign_check_check}} differ in behavior, one return ok, the other nothing.
> Ideally they'd be consistent.
>

Probably they should have been.  But that is water under the bridge
now.  If we change it, it will likely break a bunch of legacy
programs.

--
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: No way to check for CHECK constraint violations a posteriori

Dominique Devienne
In reply to this post by Richard Hipp-3
On Wed, Feb 22, 2017 at 4:47 PM, Richard Hipp <[hidden email]> wrote:

> On 2/22/17, Dominique Devienne <[hidden email]> wrote:
> > Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}}
> > checks CHECK constraints.
>
> That is now fixed on a branch.  I am still testing the changes prior
> to merging onto trunk.


Thanks Richard!

Could we please have the CHECK constraint name,
in addition to the table name, in case of a failure?
Similar to how we get that name on insert in case of a failure.
(Personally I'd prefer to have both the CHECK name and the TABLE name,
instead of one or the other, depending on whether the constraint is named
or not).

zErr = sqlite3MPrintf(db, "CHECK constraint failed in %s",
                   pTab->zName);


Pushing my luck a little bit: any chance to be able to pinpoint the
offending rows, similar to how foreign_key_check does it?

I guess one can manually grab the CHECK constraint from sqlite_master,
and do a select on the table to find those rows,
that just not very convenient :).

Thanks, --DD
_______________________________________________
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: No way to check for CHECK constraint violations a posteriori

Richard Hipp-3
On 2/22/17, Dominique Devienne <[hidden email]> wrote:
>
> Could we please have the CHECK constraint name,
> in addition to the table name, in case of a failure?
>
> Pushing my luck a little bit: any chance to be able to pinpoint the
> offending rows, similar to how foreign_key_check does it?

CHECK constraint failures are suppose to be exceedingly rare.
Elaborate error messages that pinpoint the problem are possible, but
they increase the library complexity and footprint unnecessarily.  In
the rare event that you encounter a CHECK constraint failure, you can
go back and figure out which constraint and which row is at fault
using ordinary queries.

--
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: No way to check for CHECK constraint violations a posteriori

Dominique Devienne
On Wed, Feb 22, 2017 at 5:50 PM, Richard Hipp <[hidden email]> wrote:

> On 2/22/17, Dominique Devienne <[hidden email]> wrote:
> >
> > Could we please have the CHECK constraint name,
> > in addition to the table name, in case of a failure?
> >
> > Pushing my luck a little bit: any chance to be able to pinpoint the
> > offending rows, similar to how foreign_key_check does it?
>
> CHECK constraint failures are suppose to be exceedingly rare.
> Elaborate error messages that pinpoint the problem are possible, but
> they increase the library complexity and footprint unnecessarily.  In
> the rare event that you encounter a CHECK constraint failure, you can
> go back and figure out which constraint and which row is at fault
> using ordinary queries.


OK on the rows-at-fault. No problem.

But I have some tables with many check constraints, one per column at least
basically.
Could we please at least have the constraint name? Assuming it's readily
available
at the time you generate the message, that would narrow down the "search
field"
to a single one of those CHECK constraint. And that would make it
consistent with
the normal message from an insert failure. Consistency is good right? --DD
_______________________________________________
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: No way to check for CHECK constraint violations a posteriori

Dominique Devienne
On Wed, Feb 22, 2017 at 6:09 PM Dominique Devienne <[hidden email]>
wrote:

> On Wed, Feb 22, 2017 at 5:50 PM, Richard Hipp <[hidden email]> wrote:
>
> On 2/22/17, Dominique Devienne <[hidden email]> wrote:
>
>
> >
>
>
> > Could we please have the CHECK constraint name,
>
>
> > in addition to the table name, in case of a failure?
>
>
> >
>
>
> > Pushing my luck a little bit: any chance to be able to pinpoint the
>
>
> > offending rows, similar to how foreign_key_check does it?
>
>
>
>
>
> CHECK constraint failures are suppose to be exceedingly rare.
> [...] you can go back and figure out which constraint [...]
>
>
> [...] Could we please at least have the constraint name?
> [...] And that would make it consistent with the normal message
> from an insert failure. Consistency is good right? --DD
>

I see this has been merged to trunk (w/o the constraint name).
Thank you again. --DD
_______________________________________________
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: No way to check for CHECK constraint violations a posteriori

Bob Friesenhahn
In reply to this post by Richard Hipp-3
On Wed, 22 Feb 2017, Richard Hipp wrote:
>
> CHECK constraint failures are suppose to be exceedingly rare.
> Elaborate error messages that pinpoint the problem are possible, but
> they increase the library complexity and footprint unnecessarily.  In
> the rare event that you encounter a CHECK constraint failure, you can
> go back and figure out which constraint and which row is at fault
> using ordinary queries.

In my world, constraint failures are common rather than exceedingly
rare.  We made the decision that the database should validate all of
its inputs as much as possible (including by using extension
functions) and do not depend much on intermediate wrappers to do
validation.  It is pretty normal that the program or person violating
the constraint is not very aware of the rules.

Due to the extreme weakness of sqlite when it comes to reporting
constraint failures, we use an approach where table rows are updated
one by one so that we can know which table row update failed.  This
causes other issues since whole-row or inter-table consistency checks
may temporarily fail.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users