CREATE TABLE table_unique_1 ( id NUMBER(1,0) , CONSTRAINT cu_table_unique_1_id UNIQUE (id) );Adding UNIQUE constraint to existing table:
CREATE TABLE table_unique_1 ( id NUMBER(1,0) ); ALTER TABLE table_unique_1 ADD CONSTRAINT cu_table_unique_1_id UNIQUE (id);When you create a UNIQUE constraint, Oracle automatically generates two database objects: a UNIQUE constraint and an associated UNIQUE index with the same name. This index is used internally by Oracle to enforce the uniqueness rule efficiently.
SELECT constraint_name, constraint_type, generated, index_name FROM user_constraints WHERE table_name = 'TABLE_UNIQUE_1';Query result:
+----------------------+-----------------+----------------+----------------------+ | constraint_name | constraint_type | generated | index_name | +----------------------+-----------------+----------------+----------------------+ | CU_TABLE_UNIQUE_1_ID | U | USER NAME | CU_TABLE_UNIQUE_1_ID | +----------------------+-----------------+----------------+----------------------+
SELECT index_type, uniqueness FROM user_indexes WHERE index_name = 'CU_TABLE_UNIQUE_1_ID';Query result:
+------------+------------+ | INDEX_TYPE | UNIQUENESS | +------------+------------+ | NORMAL | UNIQUE | +------------+------------+
DROP INDEX cu_table_unique_1_id;Query result:
Error starting at line 1 in command: DROP INDEX CU_TABLE_UNIQUE_1_ID Error report: SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key *Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key. *Action: drop the constraint instead of the index.
ALTER TABLE table_unique_1 DROP CONSTRAINT cu_table_unique_1_id;
SELECT index_name FROM user_indexes WHERE table_name = 'TABLE_UNIQUE_1';Query result:
INDEX_NAME --------------------
CREATE TABLE table_unique_mul_1 ( id1 NUMBER(1,0) , id2 NUMBER(1,0) , CONSTRAINT cu_table_unique_mul_1_id12 UNIQUE (ID1,ID2) );
CREATE TABLE table_unique_mul_2 ( id1 NUMBER(1,0) , id2 NUMBER(1,0) , id3 NUMBER(1,0) , CONSTRAINT cu_table_unique_mul_2_id12 UNIQUE (id1,id2) , CONSTRAINT cu_table_unique_mul_2_id3 UNIQUE (id3) );
CREATE TABLE table_unique_2 ( id NUMBER(1,0) );
CREATE INDEX in_table_unique_2_id ON table_unique_2 (id);
ALTER TABLE table_unique_2 ADD CONSTRAINT cu_table_unique_2_id UNIQUE (id) USING INDEX in_table_unique_2_id;Creating constraint with existing index - Single statement:
CREATE TABLE table_unique_2 ( id NUMBER(1,0) , CONSTRAINT cu_table_unique_2_id UNIQUE (id) USING INDEX ( CREATE INDEX in_table_unique_2_id ON table_unique_2 (id) ) );An important Oracle feature is that UNIQUE constraints can utilize non-unique indexes for enforcement. The constraint ensures uniqueness at the logical level, while the index provides the physical mechanism for efficient lookups:
SELECT index_type, uniqueness FROM user_indexes WHERE index_name = 'IN_TABLE_UNIQUE_2_ID';Query result:
+------------+------------+ | INDEX_TYPE | UNIQUENESS | +------------+------------+ | NORMAL | NONUNIQUE | +------------+------------+Important considerations when using existing indexes:
DROP INDEX in_table_unique_2_id;
Error starting at line 1 in command: DROP INDEX IN_TABLE_UNIQUE_2_ID Error report: SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key *Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key. *Action: drop the constraint instead of the index.
ALTER TABLE table_unique_2 DROP CONSTRAINT cu_table_unique_2_id;
SELECT index_name FROM user_indexes WHERE table_name = 'TABLE_UNIQUE_2';Query result:
INDEX_NAME -------------------- IN_TABLE_UNIQUE_2_ID
CREATE TABLE table_unique_3 ( id NUMBER(1,0) , CONSTRAINT cu_table_unique_3_id UNIQUE (ID) );
CREATE TABLE table_unique_3_FK ( id NUMBER(1,0) , CONSTRAINT cr_table_unique_3_fk_id FOREIGN KEY (id) REFERENCES table_unique_3 (id) -- OK );
CREATE TABLE table_cu_vs_unique_index ( id NUMBER(1,0) );
CREATE UNIQUE INDEX in_table_cu_vs_unique_index ON table_cu_vs_unique_index (id);
CREATE TABLE table_cu_vs_uindex_fk ( id NUMBER(1,0) , CONSTRAINT cr_table_cu_vs_uindex_fk_id FOREIGN KEY (id) REFERENCES table_cu_vs_unique_index (id) -- SQL Error: ORA-02270: no matching unique or primary key for this column-list );Error details:
Error starting at line 1 in command: CREATE TABLE TABLE_CU_VS_UINDEX_FK ( ID NUMBER(1,0) , CONSTRAINT CR_TABLE_CU_VS_UINDEX_FK_ID FOREIGN KEY (ID) REFERENCES TABLE_CU_VS_UNIQUE_INDEX (ID) ) Error at Command Line:4 Column:97 Error report: SQL Error: ORA-02270: no matching unique or primary key for this column-list *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table. *Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view
CREATE TABLE table_unique_4 ( id1 NUMBER(1,0) , id2 NUMBER(1,0) , CONSTRAINT cu_table_unique_4_id UNIQUE (ID1, ID2) );
INSERT INTO table_unique_4 (id1, id2) VALUES (NULL, NULL); -- OK INSERT INTO table_unique_4 (id1, id2) VALUES (NULL, NULL); -- OK! INSERT INTO table_unique_4 (id1, id2) VALUES (NULL, 1); -- OK INSERT INTO table_unique_4 (id1, id2) VALUES (1, NULL); -- OK INSERT INTO table_unique_4 (id1, id2) VALUES (1, 1); -- OK INSERT INTO table_unique_4 (id1, id2) VALUES (NULL, 1); -- NO: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated INSERT INTO table_unique_4 (id1, id2) VALUES (1, NULL); -- NO: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated INSERT INTO table_unique_4 (id1, id2) VALUES (1, 1); -- NO: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated
1 row inserted. 1 row inserted. 1 row inserted. 1 row inserted. 1 row inserted. Error starting at line 8 in command: INSERT INTO TABLE_UNIQUE_4 (id1, id2) VALUES (NULL, 1) Error report: SQL Error: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. Error starting at line 9 in command: INSERT INTO TABLE_UNIQUE_4 (id1, id2) VALUES (1, NULL) Error report: SQL Error: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key. Error starting at line 10 in command: INSERT INTO TABLE_UNIQUE_4 (id1, id2) VALUES (1, 1) Error report: SQL Error: ORA-00001: unique constraint (CU_TABLE_UNIQUE_4_ID) violated *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, You may see this message if a duplicate entry exists at a different level. *Action: Either remove the unique restriction or do not insert the key.This example demonstrates that multiple rows with (NULL, NULL) are allowed, but once a specific combination of values is inserted, subsequent attempts to insert the same combination will violate the UNIQUE constraint.