Oracle Tutorial - Constraints

Introduction to Constraints in Oracle

Constraints in Oracle are rules that enforce data integrity and ensure the accuracy and reliability of data stored in tables. They define the limitations and requirements for columns within a table, preventing invalid data entry or modification.

Types of Constraints

Oracle supports several types of constraints, each serving a specific purpose:

• NOT NULL: Ensures a column cannot contain NULL values.
• UNIQUE: Ensures all values in a column are unique across the table.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE, uniquely identifying each record in the table.
• FOREIGN KEY: Establishes referential integrity by linking a column to a primary key in another table.
• CHECK: Enforces a condition that values in a column must satisfy.

Example of Using Constraints

Let's create a sample table named Employees to demonstrate how constraints work:

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpName VARCHAR2(100) NOT NULL,
    EmpEmail VARCHAR2(100) UNIQUE,
    DeptID NUMBER,
    Salary NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID),
    CONSTRAINT chk_salary CHECK (Salary > 0)
);
                

In this example:

• EmpID is the primary key, ensuring each employee has a unique identifier.
• EmpName cannot be NULL, ensuring every employee record has a name.
• EmpEmail must be unique, preventing duplicate email addresses.
• DeptID is a foreign key referencing the Departments table, ensuring referential integrity between the Employees and Departments tables.
• Salary must be greater than zero, enforced by the chk_salary check constraint.

Using NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. It is commonly used to enforce mandatory data entry for essential columns.

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpName VARCHAR2(100) NOT NULL,
    EmpEmail VARCHAR2(100),
    CONSTRAINT emp_email_nn NOT NULL (EmpEmail)
);
                

Using UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are unique across the table. It prevents duplicate values from being inserted into the table.

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpEmail VARCHAR2(100) UNIQUE,
    EmpPhone VARCHAR2(20) UNIQUE
);
                

Using PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in the table. It is a combination of NOT NULL and UNIQUE constraints.

CREATE TABLE Departments (
    DeptID NUMBER PRIMARY KEY,
    DeptName VARCHAR2(100) NOT NULL
);

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpName VARCHAR2(100) NOT NULL,
    DeptID NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
                

Using FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a relationship between two tables by linking a column (or columns) in one table to a primary key in another table.

CREATE TABLE Departments (
    DeptID NUMBER PRIMARY KEY,
    DeptName VARCHAR2(100) NOT NULL
);

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpName VARCHAR2(100) NOT NULL,
    DeptID NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
                

Using CHECK Constraint

The CHECK constraint enforces a condition that all values in a column must satisfy. It is used to ensure that data entries meet specific criteria.

CREATE TABLE Employees (
    EmpID NUMBER PRIMARY KEY,
    EmpName VARCHAR2(100) NOT NULL,
    EmpAge NUMBER,
    CONSTRAINT chk_age CHECK (EmpAge >= 18)
);
                

Best Practices for Using Constraints

To effectively use constraints in Oracle databases, consider the following best practices:

• Define constraints that accurately reflect the business rules and requirements.
• Avoid using excessive constraints that could impact database performance.
• Regularly review and update constraints as data requirements evolve.

Conclusion

Constraints are essential for maintaining data integrity and enforcing business rules in Oracle databases. By using constraints appropriately, you can ensure data accuracy, reliability, and consistency, contributing to better overall database performance and usability.