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.rash...@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 <alvhe...@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
alter_add.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
