Re: adding 'not-null' and 'unique'-constraint for existing

On Mon, 28 Jan 2002 pilsl(at)goldfisch(dot)at wrote:

> I've an existing column that should be set to 'not-null' and 'unique'
> but I dont find out the corresponding constraint for the
> "ALTER TABLE tablename ADD CONSTRAINT .... "

Assuming you're using 7.1, add constraint will only let you add foreign
key constraint (iirc). In many cases a dump and restore of the table is
easiest (editing the dump), however, you can get the constraints if you
don't mind a little work:

The unique constraint can be effectively added by making a unique index
on the columns. Not null requires a little hack to a system table.
You'll want to set attnotnull for the column in pg_attribute, something
like UPDATE pg_attribute set attnotnull=true where attrelid=(select oid
from pg_class where relname='<table name>') and attname='<column name>';

7.2 is friendlier since you can directly add unique and you can add a
check constraint to prevent nulls.