Constraints

 

Constraints in Oracle are rules enforced on database columns to maintain data integrity and ensure the validity of the data stored in a table.

Types of Constraints –

1.      NOT NULL Constraint

Ensures that a column cannot have NULL values. Column must always  have a value.

2.      UNIQUE Constraint

Ensures that all values in a column are distinct (no duplicates allowed). Can contain NULL values, But should form unique combination with unique column/columns

e.g. -

CREATE TABLE test_unique (

    id INT,

    col1 INT UNIQUE

);

INSERT INTO test_unique VALUES (1, NULL);  -- OK

INSERT INTO test_unique VALUES (2, NULL);  -- OK (second NULL allowed)

INSERT INTO test_unique VALUES (3, 100);   -- OK

INSERT INTO test_unique VALUES (4, 100);   -- ❌ Error (duplicate value)

Error report -

ORA-00001: unique constraint (HR.SYS_C008924) violated

3.      PRIMARY KEY Constraint

Combines UNIQUE and NOT NULL (no duplicates & cannot be NULL).

4.      FOREIGN KEY Constraint (Referential Integrity)

Ensures that a value in a column must exist in another table (establishes relationships between tables).

CREATE TABLE parent_table (

    parent_id INT PRIMARY KEY,

    -- other columns

);

CREATE TABLE child_table (

    child_id INT PRIMARY KEY,

    parent_id INT,

    FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) ON DELETE CASCADE

    -- other columns

);

ON DELETE CASCADE -- Deletes the child records when parent gets deleted.

ON DELETE SET NULL -- Preserve child records when parent gets deleted.

5.      CHECK Constraint

Ensures that values in a column meet a specific condition.

6.      DEFAULT Constraint

Sets a default value if no value is provided.

 

CREATE TABLE employees (

employee_id   NUMBER CONSTRAINT pk_employee PRIMARY KEY, 

-- PRIMARY KEY (Unique + Not Null)

name          VARCHAR2(100) CONSTRAINT nn_name NOT NULL, 

-- NOT NULL (Mandatory Field)

email         VARCHAR2(100) CONSTRAINT uq_email UNIQUE,  

-- UNIQUE (No Duplicate Emails)

department_id NUMBER CONSTRAINT fk_department REFERENCES departments(department_id),

-- FOREIGN KEY

salary        NUMBER CONSTRAINT chk_salary CHECK (salary > 20000),

-- CHECK (Minimum Salary)

hire_date     DATE DEFAULT SYSDATE 

-- DEFAULT (Current Date)

);

Modifying Constraints:

You can add, drop, or modify constraints using ALTER TABLE.

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 30000);

Drop a Constraint

ALTER TABLE employees DROP CONSTRAINT chk_salary;