How do I use ALTER TABLE
to add a new column and make it unique?
views:
60answers:
3
+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
2010-09-17 11:33:30
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:
- Add column
- Fill values
- Add unique constraint
Michael Pakhantsov
2010-09-17 11:34:52
@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
2010-09-17 13:26:48
@Daniel, oracle is allow do this, sql server no allow (until you change settings)
Michael Pakhantsov
2010-09-17 13:46:37
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
2010-09-17 11:48:59