A UNIQUE constraint in Oracle ensures that no duplicate values are allowed in the specified column or combination of columns.

Key characteristics of UNIQUE constraints:

• UNIQUE constraints are primarily designed to enforce data integrity by preventing duplicate values in specified columns.
  
  
• When you create a UNIQUE constraint, Oracle automatically creates an associated UNIQUE index to enforce the constraint efficiently.
  
  
• UNIQUE constraints can be referenced by foreign key constraints, unlike standalone UNIQUE indexes.
  
  
• A UNIQUE constraint defines a uniqueness rule on one or more columns of a table, ensuring that each combination of values in these columns is unique across all rows.
  
  
• Columns that are part of a UNIQUE constraint can contain NULL values, unless explicitly defined with a NOT NULL constraint.
  
  
• For composite UNIQUE constraints, the uniqueness rule is bypassed when all columns in the constraint contain NULL values.
  
  
• UNIQUE constraints are stored as metadata in Oracle's data dictionary and can be queried through views like USER_CONSTRAINTS and ALL_CONSTRAINTS.
  

UNIQUE constraints can be created either during table creation or added to existing tables using ALTER TABLE statements. Oracle provides flexibility in naming constraints and supports both single-column and multi-column UNIQUE constraints.

Creating UNIQUE constraint during table creation:

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.

• Verify the UNIQUE constraint in the data dictionary:
  

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

• Verify the automatically created UNIQUE index:
  

  SELECT index_type, uniqueness
  FROM user_indexes
  WHERE index_name = 'CU_TABLE_UNIQUE_1_ID';
  

  Query result:

  +------------+------------+
  | INDEX_TYPE | UNIQUENESS |
  +------------+------------+
  | NORMAL     | UNIQUE     |
  +------------+------------+
  

• Attempting to drop the system-generated index will result in an error because it's required for constraint enforcement:
  

  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.
  

• Dropping the UNIQUE constraint automatically removes its associated 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
  --------------------
  

Multi-Column UNIQUE Constraints:

• For composite UNIQUE constraints spanning multiple columns, specify all columns in the constraint definition separated by commas. Oracle creates a single composite index for the entire constraint:
  

  CREATE TABLE table_unique_mul_1
  (
      id1 NUMBER(1,0)
  ,   id2 NUMBER(1,0)
  ,   CONSTRAINT cu_table_unique_mul_1_id12 UNIQUE (ID1,ID2)
  );
  

• You can also create separate UNIQUE constraints for different column combinations, with each constraint having its own associated index:
  

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

Oracle allows you to create UNIQUE constraints that utilize existing indexes, providing greater control over index properties and optimization. This approach is useful when you want to specify custom index characteristics or reuse existing indexes.

Creating constraint with existing index - Step by step:

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:

• Once a UNIQUE constraint is associated with an index, you cannot drop the index independently. Oracle protects indexes that are used for constraint enforcement:
  

  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.
  

• When you drop a UNIQUE constraint that uses a pre-existing index, the index remains in the database and is not automatically deleted:
  

  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
  

While UNIQUE constraints and UNIQUE indexes both enforce uniqueness, they serve different purposes and have distinct characteristics.

Key Differences:
UNIQUE constraints are database objects that enforce business rules and data integrity, while UNIQUE indexes are primarily performance optimization tools. Both can contain NULL values, but only UNIQUE constraints can be referenced by foreign key relationships.

Foreign Key References:
UNIQUE constraints can serve as targets for foreign key relationships, while UNIQUE indexes cannot:

• UNIQUE constraint as foreign key target:
  

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

• UNIQUE index cannot be referenced by foreign keys:
  

  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
  

Oracle's handling of NULL values in UNIQUE constraints follows specific rules that are important to understand, especially for composite constraints involving multiple columns. The behavior differs depending on whether all or only some columns contain NULL values.

NULL Value Rules:
Both UNIQUE constraints and UNIQUE indexes allow multiple rows to contain NULL values in the constrained columns. For composite constraints, the uniqueness rule is only enforced when at least one column contains a non-NULL value. When all columns in a composite UNIQUE constraint contain NULL values, the constraint is not enforced for that row.

Oracle's B*Tree indexes do not store entries where all indexed columns are NULL, which explains why multiple rows with all NULL values are permitted.

Composite UNIQUE constraint with NULL handling:

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.