On Mon, 28 Jan 2002 pilsl@goldfisch.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.
