Oracle 11g - add new column and set as unique

Question

I am facing a problem on how to set a new column to unique using Oracle 11g.

I try to use this code but it getting error:

ALTER TABLE QAS_ASSIGNED_STATE ADD UNIQUE (cuid);

You are trying to add a new column (cuid) to the table. That column should have a unique constraint? — jarlh, Mar 16 '16 at 08:29
Possible duplicate of [How to use ALTER TABLE to add a new column and make it unique?](http://stackoverflow.com/questions/3734891/how-to-use-alter-table-to-add-a-new-column-and-make-it-unique) — Chrisrs2292, Mar 16 '16 at 08:30
@jarlh I am not very sure. But what I know is the table already have unique id (ID) — Fai Zal Dong, Mar 16 '16 at 08:33
@Chrisrs2292 I have followed one of the solution but it does not work, — Fai Zal Dong, Mar 16 '16 at 08:33
A table can have one primary key, but several unique constraints/keys. — jarlh, Mar 16 '16 at 08:39

S. Nadezhnyy · Accepted Answer · 2016-03-16T08:34:29.977

3

You should define column's type. Like this:

alter table
    QAS_ASSIGNED_STATE 
 add
    cuid number NULL;

and then add constraint:

ALTER TABLE QAS_ASSIGNED_STATE ADD CONSTRAINT constraint_cuid  UNIQUE (cuid );

edited Mar 16 '16 at 08:34

answered Mar 16 '16 at 08:31

S. Nadezhnyy

Can you add a NOT NULL column without a default value? – jarlh Mar 16 '16 at 08:33
Only If your table is empty. You are right, I will fix it. Thank you – S. Nadezhnyy Mar 16 '16 at 08:34
@S.Nadezhnyy how can I add a new column and assign it as a unique which the table already have a data? can we do that? I mean the table does not empty – Fai Zal Dong Mar 17 '16 at 04:12
You shoud add column as nullable (like in my example). Then fill column with values and then create constraint. – S. Nadezhnyy Mar 17 '16 at 08:33

1 Answers1