Thread

Re: cataloguing NOT NULL constraints

Alvaro Herrera <alvherre@commandprompt.com> — 2011-07-30T00:23:14Z

Excerpts from Robert Haas's message of sáb jul 23 07:40:12 -0400 2011:
> On Sat, Jul 23, 2011 at 4:37 AM, Dean Rasheed <dean.a.rasheed@gmail.com> wrote:
> > 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.
> 
> So you'd end up with something like this?
> 
> ALTER TABLE foo ALTER COLUMN bar ADD CONSTRAINT somename NOT NULL
> 
> That works for me.  I think sticking the name of the constraint in
> there at the end of the line as Alvaro proposed would be terrible for
> future syntax extensibility - we'll be much less likely to paint
> ourselves into a corner with something like this.

Here's a patch that does things more or less in this way.  Note that
this is separate from the other patch, so while you can specify a
constraint name for the NOT NULL clause, it's not stored anywhere.

This is preliminary: there's no docs nor new tests.  Here's how it works
(you can also throw in PRIMARY KEY into the mix, but not EXCLUSION):

alvherre=# create table bar (a int);
CREATE TABLE
alvherre=# alter table bar alter column a add constraint foo_fk references foo initially deferred deferrable check (a <> 4) constraint a_uq unique constraint fnn not null;
NOTICE:  ALTER TABLE / ADD UNIQUE creará el índice implícito «a_uq» para la tabla «bar»
ALTER TABLE
alvherre=# \d bar
        Tabla «public.bar»
 Columna |  Tipo   | Modificadores 
---------+---------+---------------
 a       | integer | not null
Índices:
    "a_uq" UNIQUE CONSTRAINT, btree (a)
Restricciones CHECK:
    "bar_a_check" CHECK (a <> 4)
Restricciones de llave foránea:
    "foo_fk" FOREIGN KEY (a) REFERENCES foo(a) DEFERRABLE INITIALLY DEFERRED


The implementation is a bit dirty (at least IMO), but I don't see a way
around that, mainly because ALTER TABLE / ALTER COLUMN does not have a
proper ColumnDef to stick the Constraint nodes into; so while the other
constraints can do fine without that, it isn't very helpful for NOT NULL.
So it has to create a phony ColumnDef for transformConstraintItems to use.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support