A H M A D G O H A R

Please Wait For Loading

Ahmad Gohar Featured Image 1886_826

Understanding UNIQUE Constraints and NULL Values in Oracle

In Oracle databases, UNIQUE constraints ensure no duplicate values are inserted into the specified column(s). However, Oracle treats NULL values differently, allowing multiple NULL entries under certain conditions. Here’s a detailed explanation with examples to help you understand this behavior.


Key Points About UNIQUE Constraints and NULL Values

  1. Oracle’s Treatment of NULL:
    Oracle considers one NULL value as not equal to another NULL value. This allows multiple NULL entries in a column with a UNIQUE constraint.
  2. UNIQUE Constraints and Indexes:
    When a UNIQUE constraint is created, Oracle automatically creates a UNIQUE index. This index does not include NULL values, allowing Oracle to accept multiple NULL entries.

Example: UNIQUE Constraint on a Single Column

Step 1: Create Table and Add UNIQUE Constraint

CREATE TABLE test1 (
    col1 VARCHAR2(2),
    col2 VARCHAR2(2)
);

ALTER TABLE test1
ADD CONSTRAINT test_unique UNIQUE (col1);

Step 2: Test Insertions

  1. Insert a non-NULL value: INSERT INTO test1 VALUES ('a', 'a'); -- Success
  2. Insert a duplicate non-NULL value: INSERT INTO test1 VALUES ('a', 'a'); -- Error: ORA-00001: unique constraint (TEST_UNIQUE) violated
  3. Insert NULL values: INSERT INTO test1 VALUES (NULL, 'a'); -- Success INSERT INTO test1 VALUES (NULL, 'a'); -- Success

Result:

Multiple NULL values are allowed because Oracle’s UNIQUE index does not consider NULL values.


Example: Composite UNIQUE Constraint

Step 1: Drop Existing Constraint and Add Composite UNIQUE Constraint

ALTER TABLE test1
DROP CONSTRAINT test_unique;

TRUNCATE TABLE test1;

ALTER TABLE test1
ADD CONSTRAINT unique2 UNIQUE (col1, col2);

Step 2: Test Insertions

  1. Insert multiple (NULL, NULL) pairs: INSERT INTO test1 VALUES (NULL, NULL); -- Success INSERT INTO test1 VALUES (NULL, NULL); -- Success
  2. Insert (NULL, 'a') pairs: INSERT INTO test1 VALUES (NULL, 'a'); -- Success INSERT INTO test1 VALUES (NULL, 'a'); -- Error: ORA-00001: unique constraint (UNIQUE2) violated

Result:

  • Oracle allows multiple (NULL, NULL) pairs.
  • However, when one column in the composite constraint is NOT NULL, Oracle enforces uniqueness for that combination.

Why Does This Happen?

  1. NULLs and Indexes:
    Oracle’s UNIQUE index does not index NULL values. This allows Oracle to accept multiple (NULL, NULL) combinations.
  2. Composite Constraints:
    In composite constraints, the combination of column values must be unique. If one column is NULL, Oracle evaluates the other column(s) for uniqueness.

Practical Implications

  1. Designing Tables:
    When designing tables with UNIQUE constraints, understand how NULL values might impact data integrity.
  2. Data Validation:
    Use NOT NULL constraints in conjunction with UNIQUE constraints to prevent unintended duplicate rows with NULLs.
  3. Querying with NULLs:
    Remember that querying for NULL values in a UNIQUE constraint requires special handling, as NULLs are not indexed.

Conclusion

Oracle’s behavior with UNIQUE constraints and NULL values allows flexibility but requires careful consideration during schema design. Understanding the interaction between constraints, indexes, and NULLs ensures data integrity while avoiding unexpected errors.

Would you like assistance with other Oracle constraint behaviors or schema design tips? 😊

author avatar
Ahmad Gohar
With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

Leave A Comment