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.g.johns...@gmail.com <mailto:david.g.johns...@gmail.com> > > wrote: > > > On Tuesday, October 9, 2018, Imai, Yoshikazu > <imai.yoshik...@jp.fujitsu.com <mailto:imai.yoshik...@jp.fujitsu.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
