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 something about the complexity that I can make indexes?
Regards
Russell Smith
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
