Thread: Constraint violations don't report the value that violates

Hi,

I've would find it useful if check constraints and unique constraints
would give a value which is violating the constraint.

Foreign keys give a value that is failing for the foreign key, is there
a reason that other constraints don't do the same thing?

example psql session from 8.3beta4;

# create table test (x integer ,primary key (x));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
# insert into test values (1);
INSERT 0 1
# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
STATEMENT:  insert into test values (1);
# create table test2 (y integer references test(x));
CREATE TABLE
# insert into test2 values (2);
ERROR:  insert or update on table "test2" violates foreign key
constraint "test2_y_fkey"
DETAIL:  Key (y)=(2) is not present in table "test".
STATEMENT:  insert into test2 values (2);
# create table test3 (z integer check (z>0));
CREATE TABLE
# insert into test3 values (-1);
ERROR:  new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT:  insert into test3 values (-1);
# insert into test3 select g from generate_series(-1,1) as g;
ERROR:  new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT:  insert into test3 select g from generate_series(-1,1) as g;
# insert into test2 select g from generate_series(-1,1) as g;
ERROR:  insert or update on table "test2" violates foreign key
constraint "test2_y_fkey"
DETAIL:  Key (y)=(-1) is not present in table "test".
STATEMENT:  insert into test2 select g from generate_series(-1,1) as g;


Notice that the foreign key case always reports the value that is
violating.  None of the other cases do.  If all cases could report the
error it would assist greatly in bulk load/INSERT INTO SELECT type queries.

Is this possible or difficult?  or has nobody had the inclination?

Thanks

Russell Smith

Russell Smith <mr-russ@pws.com.au> writes:
> I've would find it useful if check constraints and unique constraints
> would give a value which is violating the constraint.

> Foreign keys give a value that is failing for the foreign key, is there
> a reason that other constraints don't do the same thing?

It's not very well-defined, for instance what would you report for

    CHECK some_black_box_function(a, b, c+d)

FKs are constrained by the spec syntax to only involve simple column
values, but this is not the case for either CHECK or unique indexes.

Unique partial indexes would make things even more interesting.

            regards, tom lane

Tom Lane wrote:
>> Foreign keys give a value that is failing for the foreign key, is there
>> a reason that other constraints don't do the same thing?
>>
>
> It's not very well-defined, for instance what would you report for
>
>     CHECK some_black_box_function(a, b, c+d)
>
> FKs are constrained by the spec syntax to only involve simple column
> values, but this is not the case for either CHECK or unique indexes.
>
> Unique partial indexes would make things even more interesting.
>
>             regards, tom lane
>
I would have thought that the inputs would be well defined. In the
example, a,b,c and d. This would be the same for any partial index.

So instead of this report where test3_z_check is the black box function
above;
ERROR:  new row for relation "test3" violates check constraint
"test3_z_check"
STATEMENT:  insert into test3 select g from generate_series(-1,1) as g;

You would get an error like this;
ERROR:  new row for relation "test3" violates check constraint
"test3_z_check"
DETAIL: Input (a) = 'fred', (b) = 2, (c) = 6, (d) = -1
STATEMENT:  insert into test3 select g from generate_series(-1,1) as g;

The data must have been attempted to be inserted with values to violate
the check.  I can't see how it's possible to have an input set of data
that is not a specific set of values for a check/unique constraint.

some_black_box_function must be immutable and can only take column data elements from the parent table. Or am I missing
somethingabout the complexity that I can make indexes? 

Regards

Russell Smith