Tuesday, April 9, 2019

How to create a unique constraint with an existing index (the USING-clause)



Create the index:
CREATE UNIQUE INDEX MYTABLE_IDX1 ON MYTABLE
(ID,CATEGORY)
LOCAL
TABLESPACE USERS
ONLINE;

Create a unique constraint:
ALTER TABLE MYTABLE ADD (
CONSTRAINT UK_MYTABLE
UNIQUE (ID,CATEGORY)
USING INDEX MYTABLE_IDX1
);

For a primary key constraint the syntax is similar.
In this example, an index named MYTABLE_PK will be automatically created on the fly:
ALTER TABLE MYTABLE ADD (
CONSTRAINT MYTABLE_PK
PRIMARY KEY (ID)
USING INDEX);

No comments:

Post a Comment