views:

60

answers:

3

How do I use ALTER TABLE to add a new column and make it unique?

+3  A: 

Depends on the DBMS, but I think the following is quite portable:

ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name ADD UNIQUE (column_name)

If you want to give a name to the UNIQUE constraint, you could replace the last command with this:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name)
Daniel Vassallo
A: 

if table is empty

  ALTER TABLE ADD (FieldName Type)
  ALTER TABLE ADD CONSTRAINT UNIQUE(FieldName)

If you have data in table you need to this in three steps:

  1. Add column
  2. Fill values
  3. Add unique constraint
Michael Pakhantsov
@Michael: As @APC noted, `UNIQUE` allows multiple `NULL` values, so there is no need to do it in three steps if there is data in the table.
Daniel Vassallo
@Daniel, oracle is allow do this, sql server no allow (until you change settings)
Michael Pakhantsov
A: 

It is a two step process: add the new coloumn, then add the constraint. Because UNIQUE constraints permit nulls it doesn't matter whether the table is populated:

SQL> select count(*) from t23
  2  /

  COUNT(*)
----------
         2


SQL> alter table t23
  2      add new_col number
  3  /

Table altered.

SQL> alter table t23
  2      add constraint t23_uk unique (new_col)
  3  /

Table altered.

SQL>
APC