Bug report: inconsistent error message across different CONSTRAINT types

(1) By kevinlang on 2021-04-19 20:24:00 [source]

Hello!

Often when hitting a constraint, it is useful to know which constraint you hit (if you named it) so that the application code can respond accordingly for e.g., showing an appropriate error message.

Unfortunately, SQLite3 is not consistent in giving us a workable error message for this purpose. It does for some, but not for others.

Here is the behavior for some of the constraints in column-constraint.

NOT NULL - constraint name NOT in error message

sqlite> create table a(b constraint c not null);
sqlite> insert into a values (null);
Error: NOT NULL constraint failed: a.b

CHECK - constraint name IS in error message

sqlite> create table a(b constraint c check (b != 0));
sqlite> insert into a values (0);
Error: CHECK constraint failed: c

UNIQUE - constriant name NOT in error message

sqlite> create table a(b constraint c unique);
sqlite> insert into a values (1);
sqlite> insert into a values (1);
Error: UNIQUE constraint failed: a.b

Are there any plans on standardizing the error messages in the case where the constraint name is specified?

Thanks,

Kevin

(2) By warmwaffles on 2021-04-21 18:01:33 in reply to 1 [link] [source]

Bumping this as I am interested in a solution.

(3) By Clemens Ladisch (cladisch) on 2021-05-02 15:06:45 in reply to 1 [link] [source]

You are not supposed to try to parse error messages in code.

NOT NULL and UNIQUE messages do not show the constraint name because the constraint is already identified by its columns.

(4) By anonymous on 2021-05-02 18:34:36 in reply to 3 [link] [source]

You are not supposed to try to parse error messages in code.

Why not? As a developer we may find such errors acceptable and workable, but when presenting errors in an application to an end-user they really should be more userfriendly. And when said error is caused by bad or missing input it would be nice if the cursor could be placed in the offending field.

(5) By Clemens Ladisch (cladisch) on 2021-05-03 14:09:10 in reply to 4 [link] [source]

Why not?

It is likely to break in future versions. The exact syntax of error messages is not part of the API.

The only practical way of giving the calling code semantic information about the error is through additional error fields, such as the standard SQLSTATE, or Postgres' PG_DIAG_STATEMENT_POSITION. SQLite does not have those fields, so there is no good solution.

(6) By anonymous on 2021-05-03 14:35:17 in reply to 5 [link] [source]

Yes, breakage is a risk.

so there is no good solution

I use a lot of triggers - with the RAISE() function you can produce very detailed messages.

You can also test things that were/are not supported in SQLite. My usage of triggers actually started when there was no foreign key support.