CODEWITHSUNDEEP
oracle-databaseoracle11gunique-constraint
Anita
Anita

Reputation: 185

oracle change a column to unique

Table has been created in system this way

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)
);

The new crete table statement is as below:

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)
);

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. 

ALTER TABLE INSTANCES ADD CONSTRAINT ab UNIQUE ( INSTANCEID);

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.

Please help me to Alter the table as required above. Thanks!

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';

"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"

Upvotes: 1

Views: 3712

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You have already stated that INSTANCEID is supposed to be UNIQUE, so a constraint has been created.

CREATE TABLE INSTANCES
(
    DM  INTEGER  NOT NULL,
    INSTANCEID  VARCHAR2(512)  NOT NULL  UNIQUE, -- UNIQUE constraint
    INSTANCENAME  VARCHAR2(64)  NOT NULL  UNIQUE,
    HOSTNAME  VARCHAR2(32)  NOT NULL,
    CONSTRAINT  PK_INSTANCES  PRIMARY KEY  (INSTANCEID, HOSTNAME)
);

Edit: Ok, after reading your comment, try this:

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'
  AND con.constraint_type = 'U'
;

It will list UNIQUE constraints and associated columns for INSTANCE table. Please check if there is a unique constraint on the INSTANCEID column (and if that constraint has no other associated columns).

Example at SQLFiddle: http://sqlfiddle.com/#!4/43b43/6

Edit #2: creating named constraints, all options:

-- CREATE TABLE - "In Line" Constraints

CREATE TABLE ports (
  ID NUMBER CONSTRAINT PORT_ID_PK PRIMARY KEY,
  NAME VARCHAR2(20)
);

CREATE TABLE ports (
  ID NUMBER,
  NAME VARCHAR2(20) CONSTRAINT NAME_NN NOT NULL
);

CREATE TABLE ports (
  ID NUMBER,
  NAME VARCHAR2(20) CONSTRAINT NAME_UQ UNIQUE
);

CREATE TABLE ports (
  ID NUMBER,
  STATUS NUMBER CONSTRAINT PROPER_STATUS_CK
    CHECK (STATUS IN (4, 5))
);

CREATE TABLE ships (
  SHIP_ID NUMBER,
  NAME VARCHAR2(20),
  HOME_PORT_ID NUMBER CONSTRAINT SHIP_PORT_FK
    REFERENCES PORTS (ID)
);


-- CREATE TABLE - "Out of Line" Constraints

CREATE TABLE ports (
  ID NUMBER,
  NAME VARCHAR2(20),
  CONSTRAINT PORT_ID_PK PRIMARY KEY (ID)
);

-- NOT NULL constraints can not be created "Out of Line"!

CREATE TABLE ports (
  ID NUMBER,
  NAME VARCHAR2(20),
  CONSTRAINT NAME_UQ UNIQUE (NAME)
);

CREATE TABLE ports (
  ID NUMBER,
  STATUS NUMBER,
  CONSTRAINT PROPER_STATUS_CK
    CHECK (STATUS IN (4, 5))
);

CREATE TABLE ships (
  SHIP_ID NUMBER,
  NAME VARCHAR2(20),
  HOME_PORT_ID NUMBER,
  CONSTRAINT SHIP_PORT_FK FOREIGN KEY
    (HOME_PORT_ID) REFERENCES PORTS (ID)
);

-- ALTER TABLE - "In Line" Constraints

ALTER TABLE PORTS MODIFY ID
  CONSTRAINT PORT_ID_PK PRIMARY KEY;

ALTER TABLE PORTS MODIFY NAME
  CONSTRAINT NAME_NN NOT NULL;

ALTER TABLE PORTS MODIFY NAME
  CONSTRAINT NAME_UQ UNIQUE;

ALTER TABLE SHIPS MODIFY HOME_PORT_ID
  CONSTRAINT SHIP_PORT_FK REFERENCES PORTS (ID);

-- ALTER TABLE - "Out of Line" Constraints

ALTER TABLE PORTS ADD CONSTRAINT
  PORT_ID_PK PRIMARY KEY (ID);

-- NOT NULL constraints can not be created "Out of Line"!

ALTER TABLE PORTS ADD CONSTRAINT
  NAME_UQ UNIQUE (NAME);

ALTER TABLE PORTS ADD
  CONSTRAINT PROPER_STATUS_CK
  CHECK (STATUS IN (4, 5));

ALTER TABLE SHIPS ADD CONSTRAINT SHIP_PORT_FK
  FOREIGN KEY (HOME_PORT_ID)
  REFERENCES PORTS (ID);

NOT NULL constraints cannot be create of out line.

Upvotes: 1

Related Questions