Re: cataloguing NOT NULL constraints

On 22 July 2011 22:28, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Fri, Jul 22, 2011 at 4:39 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> Excerpts from Robert Haas's message of vie jul 22 12:14:30 -0400 2011:
>>> On Thu, Jul 21, 2011 at 7:51 PM, Alvaro Herrera
>>> <alvherre(at)commandprompt(dot)com> wrote:
>>> >> I think that there probably ought to be a way to display the NOT NULL
>>> >> constraint names (perhaps through \d+). For example, if you're
>>> >> planning to support NOT VALID on top of this in the future, then there
>>> >> needs to be a way to get the constraint's name to validate it.
>>> >
>>> > Absolutely true.  Another thing that needs to be done here is to let the
>>> > ALTER TABLE and ALTER DOMAIN commands use the constraint names; right
>>> > now, they simply let you add the constraint but not specify the name.
>>> > That should probably be revisited.
>>>
>>> That, at least, seems like something that should be fixed before commit.
>>
>> Hmm, which point, Dean's or mine?  Dean was saying that the name should
>> be displayed by some flavor of \d;
>
> That might not be 100% necessary for the initial commit, but seems
> easy to fix, so why not?
>

Agreed.

>> mine was that we need a command such
>> as
>>
>> ALTER TABLE foo ALTER COLUMN bar SET NOT NULL name_of_notnull_constr
>>
>> where the last bit is what's new.
>
> Well, if you don't have that, I don't see how you have any chance of
> pg_dump working correctly.

Ah yes, pg_dump's dumpConstraint() needs a clause to alter a table
adding a named NOT NULL constraint (and the DOMAIN case should be
preserving the constraint's name too). So it looks like some new
syntax for ALTER TABLE to add named NOT NULL constraints is probably
needed before this can be committed.

>  Though I think it should use the table
> constraint syntax:
>
> CONSTRAINT name_of_notnull_constr constraint_definition
>
> I'm not exactly sure what to propose for the constraint_definition.
> Perhaps just:
>
> CONSTRAINT name_of_notnull_constr NOT NULL column_name
>

That looks wrong to me, because a NOT NULL constraint is a column
constraint not a table constraint. The CREATE TABLE syntax explicitly
distinguishes these 2 cases, and only allows NOT NULLs in column
constraints. So from a consistency point-of-view, I think that ALTER
TABLE should follow suit.

So the new syntax could be:

ALTER TABLE table_name ALTER [COLUMN] col_name ADD column_constraint

where column_constraint is the same as in CREATE TABLE (i.e., allowing
all the other constraint types at the same time).

It looks like that approach would probably lend itself to more
code-reusability too, especially once we start adding options to the
constraint.

Regards,
Dean