CODEWITHSUNDEEP
sqluniquealter-table
Innova
Innova

Reputation: 4971

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

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

Upvotes: 18

Views: 39789

Answers (4)

Antonio Bardazzi
Antonio Bardazzi

Reputation: 3236

You can do it with a single SQL statement (at least with MySQL):

ALTER TABLE `people` ADD COLUMN `personal_code` VARCHAR(50) UNIQUE AFTER `surname`;

Upvotes: 2

APC
APC

Reputation: 146239

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>

Upvotes: 1

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

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

Upvotes: 5

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

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)

Upvotes: 30

Related Questions