StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POoracle change a column to unique
text
Body
copied!<p>Table has been created in system this way</p> <pre><code>CREATE TABLE INSTANCES ( DM INTEGER NOT NULL, INSTANCEID VARCHAR2(512) NOT NULL, INSTANCENAME VARCHAR2(64) NOT NULL UNIQUE, HOSTNAME VARCHAR2(32) NOT NULL, CONSTRAINT PK_INSTANCES PRIMARY KEY (INSTANCEID, HOSTNAME) ); </code></pre> <p>The new crete table statement is as below:</p> <pre><code>CREATE TABLE INSTANCES ( DM INTEGER NOT NULL, INSTANCEID VARCHAR2(512) NOT NULL UNIQUE, INSTANCENAME VARCHAR2(64) NOT NULL UNIQUE, HOSTNAME VARCHAR2(32) NOT NULL, CONSTRAINT PK_INSTANCES PRIMARY KEY (INSTANCEID, HOSTNAME) ); </code></pre> <p>The differnce is INSTANCEID has UNIQUE in it. How do i Alter the table? I used the below statement and it did not work for me. </p> <pre><code>ALTER TABLE INSTANCES ADD CONSTRAINT ab UNIQUE ( INSTANCEID); </code></pre> <p>It gave an error: ALTER TABLE INSTANCES ADD CONSTRAINT ab UNIQUE ( INSTANCEID) Error report: SQL Error: ORA-02261: such unique or primary key already exists in the table 02261. 00000 - "such unique or primary key already exists in the table" *Cause: Self-evident. *Action: Remove the extra key.</p> <p>Please help me to Alter the table as required above. Thanks!</p> <p>Here is the output of SELECT con.constraint_name, col.column_name, con.constraint_type FROM user_cons_columns col JOIN user_constraints con ON (col.constraint_name = con.constraint_name) WHERE col.table_name = 'INSTANCES';</p> <pre><code>"CONSTRAINT_NAME","COLUMN_NAME","CONSTRAINT_TYPE" "SYS_C0016531","DM","C" "SYS_C0016532","INSTANCEID","C" "SYS_C0016533","INSTANCENAME","C" "SYS_C0016534","HOSTNAME","C" "PK_INSTANCES","HOSTNAME","P" "PK_INSTANCES","INSTANCEID","P" "SYS_C0016536","INSTANCENAME","U" </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload