RE: Why we allow CHECK constraint contradiction?

Thanks for replying!

On Tue, Oct 9, 2018 at 5:58 PM, Corey Huinker wrote:
> On Wed, Oct 10, 2018 at 1:44 AM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com> >
> wrote:
>
>
> On Tuesday, October 9, 2018, Imai, Yoshikazu
> <imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com <mailto:imai(dot)yoshikazu(at)jp(dot)fujitsu(dot)com>
> > wrote:
>
> Are there any rows which can satisfy the ct's CHECK
> constraint? If not, why we
> allow creating table when check constraint itself is
> contradicted?
>
>
>
> I'd bet on it being a combination of complexity and insufficient
> expected benefit. Time is better spent elsewhere. Mathmatically
> proving a contradiction in software is harder than reasoning about it
> mentally.
>
>
> I've actually used that as a feature, in postgresql and other databases,
> where assertions were unavailable, or procedural code was unavailable
> or against policy.
>
> Consider the following:
>
>
> CREATE TABLE wanted_values ( x integer );
>
> INSERT INTO wanted_values VALUES (1), (2), (3);
>
>
>
>
> CREATE TABLE found_values ( x integer );
>
> INSERT INTO found_values VALUES (1), (3);
>
>
>
>
> CREATE TABLE missing_values (
>
> x integer,
>
> CONSTRAINT contradiction CHECK (false)
>
> );
>
>
>
>
> INSERT INTO missing_values
>
> SELECT x FROM wanted_values
>
> EXCEPT
>
> SELECT x FROM found_values;
>
>
>
>
> gives the error
>
>
> ERROR: new row for relation "missing_values" violates check
> constraint "contradiction"
>
> DETAIL: Failing row contains (2).
>
>
> Which can be handy when you need to fail a transaction because of bad
> data and don't have branching logic available.

That's an interesting using! So, there are useful case of constraint
contradiction table not only for time shortage/difficulties of
implementing mathematically proving a contradiction.

--
Yoshikazu Imai