Quick flyby here... On Tuesday, May 3, 2016, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Vitaly Burovoy <vitaly.buro...@gmail.com <javascript:;>> writes: > > On 4/27/16, Alvaro Herrera <alvhe...@2ndquadrant.com <javascript:;>> > wrote: > >> Point 2 is where things differ from what I remember; my (possibly > >> flawed) understanding was that there's no difference between those > >> things. Many (maybe all) of the things from this point on are probably > >> fallout from that one change. > > > It is just mentioning that CHECK constraints have influence on > > nullability characteristic, but it differs from NNC. > > NNC creates CHECK constraint, but not vice versa. You can create > > several CHECK "col IS NOT NULL" constraints, but only one NNC (several > > ones by inheritance only?). And DROP NOT NULL should drop only those > > CHECK that is linked with NNC (and inherited), but no more (full > > explanation is in my initial letter). Either it's one, or it's not... > This seems to me to be a most curious reading of the standard. > SQL:2011 11.4 <column definition> syntax rule 17a says > > If a <column constraint definition> is specified that contains > the <column constraint> NOT NULL, then it is equivalent to the > following <table constraint definition>: > > CND CHECK ( C IS NOT NULL ) CA > > As a rule, when the SQL spec says "equivalent", they do not mean "it's > sort of like this", they mean the effects are indistinguishable. In > particular, I see nothing whatsoever saying that you're not allowed to > write more than one per column. Does it define how DROP NOT NULL is supposed to behave? I agree that the behavior of a column NNC is identical to a similar constraint defined on the table: but if drop not null doesn't impact table constraints then the concept of perfect equality is already lost. > So I don't like the proposal to add an attnotnullid column to > pg_attribute. What we'd talked about earlier was converting attnotnull > into, effectively, a hint flag saying that there's at least one NOT NULL > constraint attached to the column. > Have we considered making it a table constraint and giving it a name? We already handle that case without difficulty. Not looking for a detailed explanation. David J.
