Detecting multiple CHECK failures

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

Detecting multiple CHECK failures

phaworth
I'm a great believer in using CHECK constraints to do as much validation as
possible within the database rather than code it in my application.

However, I think I'm right in saying that as soon as a CHECK constraint
fails, an error is returned to my application so no other CHECK constraints
are executed  In a data entry type of application, this isn't ideal as
users would prefer to see all the errors they need to correct in one
message.

I can't think of a way round this but wondering if anyone has found a
technique to return all CHECK constraint errors at once.

Pete
lcSQL Software <http://www.lcsql.com>
Home of SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
_______________________________________________
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: Detecting multiple CHECK failures

Simon Slavin-3

On 7 Oct 2014, at 10:00pm, Peter Haworth <[hidden email]> wrote:

> I'm a great believer in using CHECK constraints to do as much validation as
> possible within the database rather than code it in my application.
>
> However, I think I'm right in saying that as soon as a CHECK constraint
> fails, an error is returned to my application so no other CHECK constraints
> are executed  In a data entry type of application, this isn't ideal as
> users would prefer to see all the errors they need to correct in one
> message.

For most ways in which SQLite can refuse to do something, you have no way to know why it refused.  The results don't include the name of a constraint which failed, or anything else of any use.  You simply get a result code which tells you that the operation failed because of the data in your command (rather than because the command had bad syntax or referred to a table/index/column which didn't exist).

> I can't think of a way round this but wondering if anyone has found a
> technique to return all CHECK constraint errors at once.

It would appear that in SQLite the CHECK constraints are useful only in ensuring your database doesn't reflect things that are impossible.  It is of no use at all in knowing why a command is rejected.

Ideally, if a result code indicates a constraint failure, there would be a way to retrieve a list of the names of the constraints which would have been violated.  However this is not possible in SQLite3 at all without a major rewrite.  SQLite3 just gets a binary indication of whether any constraints were violated.

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: Detecting multiple CHECK failures

gwenn-6
Hello,
I think that SQLite reports the first constraint which fails:

http://sqlite.org/changes.html
2012-05-14 (3.7.12)
Report the name of specific CHECK constraints that fail.

sqlite> CREATE TABLE test (data TEXT CONSTRAINT notEmpty CHECK
(length(data) > 0));
sqlite> INSERT INTO test VALUES ('');
Error: CHECK constraint failed: notEmpty

Regards.

On Tue, Oct 7, 2014 at 11:11 PM, Simon Slavin <[hidden email]> wrote:

>
> On 7 Oct 2014, at 10:00pm, Peter Haworth <[hidden email]> wrote:
>
>> I'm a great believer in using CHECK constraints to do as much validation as
>> possible within the database rather than code it in my application.
>>
>> However, I think I'm right in saying that as soon as a CHECK constraint
>> fails, an error is returned to my application so no other CHECK constraints
>> are executed  In a data entry type of application, this isn't ideal as
>> users would prefer to see all the errors they need to correct in one
>> message.
>
> For most ways in which SQLite can refuse to do something, you have no way to know why it refused.  The results don't include the name of a constraint which failed, or anything else of any use.  You simply get a result code which tells you that the operation failed because of the data in your command (rather than because the command had bad syntax or referred to a table/index/column which didn't exist).
>
>> I can't think of a way round this but wondering if anyone has found a
>> technique to return all CHECK constraint errors at once.
>
> It would appear that in SQLite the CHECK constraints are useful only in ensuring your database doesn't reflect things that are impossible.  It is of no use at all in knowing why a command is rejected.
>
> Ideally, if a result code indicates a constraint failure, there would be a way to retrieve a list of the names of the constraints which would have been violated.  However this is not possible in SQLite3 at all without a major rewrite.  SQLite3 just gets a binary indication of whether any constraints were violated.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
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: Detecting multiple CHECK failures

Clemens Ladisch
In reply to this post by phaworth
Peter Haworth wrote:
> I'm a great believer in using CHECK constraints to do as much validation as
> possible within the database rather than code it in my application.
>
> However, I think I'm right in saying that as soon as a CHECK constraint
> fails, an error is returned to my application so no other CHECK constraints
> are executed  In a data entry type of application, this isn't ideal as
> users would prefer to see all the errors they need to correct in one
> message.

SQL constraints were designed to catch _programming_ errors, not _user_
errors.

> I can't think of a way round this but wondering if anyone has found a
> technique to return all CHECK constraint errors at once.

When you have interdependent constraints, such as "must be a closed
polygon" and "area must be at least 10", it might not make sense (or
even be impossible) to check all constraints.


Regards,
Clemens
_______________________________________________
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: Detecting multiple CHECK failures

phaworth
In reply to this post by phaworth
Hi Simon,
Actually, I do get the CHECK constraint name returned to me in the error
message otherwise, as you say, it would be impossible to find out what
failed.

I use a translation table in my application to reformat the SQLite error
message to a more suitable format to present to my users based on the
constraint name.

On Wed, Oct 8, 2014 at 9:00 AM, <[hidden email]> wrote:

> For most ways in which SQLite can refuse to do something, you have no way
> to know why it refused.  The results don't include the name of a constraint
> which failed, or anything else of any use.  You simply get a result code
> which tells you that the operation failed because of the data in your
> command (rather than because the command had bad syntax or referred to a
> table/index/column which didn't exist).




Pete
lcSQL Software <http://www.lcsql.com>
Home of  SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
_______________________________________________
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: Detecting multiple CHECK failures

Nico Williams
In reply to this post by Clemens Ladisch
On Wed, Oct 8, 2014 at 8:01 AM, Clemens Ladisch <[hidden email]> wrote:

> Peter Haworth wrote:
>> I'm a great believer in using CHECK constraints to do as much validation as
>> possible within the database rather than code it in my application.
>>
>> However, I think I'm right in saying that as soon as a CHECK constraint
>> fails, an error is returned to my application so no other CHECK constraints
>> are executed  In a data entry type of application, this isn't ideal as
>> users would prefer to see all the errors they need to correct in one
>> message.
>
> SQL constraints were designed to catch _programming_ errors, not _user_
> errors.

Reference please?

Anyways, that might be, but duplicating constraints checking in the
calling program is rather obnoxious.  After all, SQL is extremely
expressive, and one of the main reasons to use it -- if you have to
duplicate SQL code in the calling program because of insufficiently
rich error reporting then why use SQL.

Also, even if your assertion is correct, there's no reason that the
RDBMS can't report check constraint failures more usefully.  The point
of being able to name constraints is almost certainly to enable this
(which is evidence that your assertion is likely false, or if true
about initial design, irrelevant now).

>> I can't think of a way round this but wondering if anyone has found a
>> technique to return all CHECK constraint errors at once.
>
> When you have interdependent constraints, such as "must be a closed
> polygon" and "area must be at least 10", it might not make sense (or
> even be impossible) to check all constraints.

This is true, but if there's no way to order the checking of
constraints then it doesn't matter for this discussion.

Nico
--
_______________________________________________
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: Detecting multiple CHECK failures

Nico Williams
In reply to this post by phaworth
On Wed, Oct 8, 2014 at 12:23 PM, Peter Haworth <[hidden email]> wrote:
> Actually, I do get the CHECK constraint name returned to me in the error
> message otherwise, as you say, it would be impossible to find out what
> failed.
>
> I use a translation table in my application to reformat the SQLite error
> message to a more suitable format to present to my users based on the
> constraint name.

This is really the best you can manage.  The key is to be able to
extract the name of the failing constraint, the name of the unique
index in which a conflict arose, the string passed to RAISE() in a
trigger, then map it to a suitable error message for your application.

If a check expression is complex, well, what can you do but duplicate
parts of it in the application, but if you only have to do that for
error formatting, then using check constraints is still a big win.

Nico
--
_______________________________________________
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: Detecting multiple CHECK failures

James K. Lowden
In reply to this post by Clemens Ladisch
On Wed, 08 Oct 2014 15:01:39 +0200
Clemens Ladisch <[hidden email]> wrote:

> SQL constraints were designed to catch _programming_ errors, not
> _user_ errors.

Neither and both, actually.  Database theory doesn't distinguish between
different sources of invalid input.  

Constraints enforce consistency. They prevent the database from
reaching a state that's "out of bounds" as defined by its own rules.  

One way to think about a database is as one giant variable of a
single type. All changes have to continue to meet the rules of that
type, else it wouldn't be that type anymore.  It would be NaD: not a
database.  

Sometimes the user could know.  There's no February 31 (except in
MySQL).  Sometimes the program could know, as when the foreign key
referent must be inserted before the reference.  Sometimes the program
can't know, as when another user reserved seat 32B since it was
displayed as available.  

--jkl
_______________________________________________
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: Detecting multiple CHECK failures

phaworth
In reply to this post by phaworth
On Thu, Oct 9, 2014 at 9:00 AM, <[hidden email]> wrote:

> Message: 6
> Date: Wed, 8 Oct 2014 14:40:57 -0500
> From: Nico Williams <[hidden email]>
> To: General Discussion of SQLite Database <[hidden email]>
> Subject: Re: [sqlite] Detecting multiple CHECK failures
> Message-ID:
>         <
> [hidden email]>
> Content-Type: text/plain; charset=UTF-8
>
>
> > SQL constraints were designed to catch _programming_ errors, not _user_
> > errors.
>
> Reference please?
>

I wondered about that too.  In any case, an error is an error no matter
whether it was caused by a programming mistake or invalid user data.


>
> Anyways, that might be, but duplicating constraints checking in the
> calling program is rather obnoxious.  After all, SQL is extremely
> expressive, and one of the main reasons to use it -- if you have to
> duplicate SQL code in the calling program because of insufficiently
> rich error reporting then why use SQL.
>

Yes, my thoughts too.  I'd rather have SQLite take care of as much data
integrity checking as possible rather than code it in my program. That way,
it doesn't matter what programs access the database, the same integrity
checks will be enforced.

>
> >> I can't think of a way round this but wondering if anyone has found a
> >> technique to return all CHECK constraint errors at once.
> >
>


> I haven't seen a reply answering this question so I guess I'll have to
> assume it's not possible.  Sure would be nice though.  I plan to continue
> relying on SQLite constraints for my error checking but it's pretty
> obnoxious to report an error to the user only for him/her to be presented
> with another error after fixing the first one.
>


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users