Table Constraints in Oracle SQL

Table constraints in Oracle SQL are rules that enforce data integrity and consistency within a database. They ensure that the data inserted into a table meets specific criteria, preventing invalid or inconsistent data from being stored. Understanding and applying these constraints is crucial for maintaining the accuracy and reliability of your database.

1. PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It ensures that the column or set of columns specified as the primary key contains only unique values and cannot contain NULL values. A table can have only one primary key, which can consist of single or multiple columns.

Example:

Creating a table with a PRIMARY KEY constraint on the 'EmployeeID' column:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50)
);

2. FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a link between two tables by referencing the primary key of another table. It ensures that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table. This constraint helps maintain referential integrity, ensuring that relationships between tables are valid and consistent.

Example:

Creating a table with a FOREIGN KEY constraint that references the 'DepartmentID' column in the 'Departments' table:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    DepartmentID NUMBER,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

3. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column or a set of columns are unique. Unlike the PRIMARY KEY constraint, a UNIQUE constraint can accept NULL values. A table can have multiple UNIQUE constraints, each applied to different columns or combinations of columns.

Example:

Creating a table with a UNIQUE constraint on the 'Email' column:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Email VARCHAR2(100) UNIQUE
);

4. CHECK Constraint

The CHECK constraint enforces a condition on the values that can be inserted into a column. It ensures that the data meets a specific requirement defined by a Boolean expression. This constraint can be applied to a single column or multiple columns, providing flexibility in defining custom validation rules.

Example:

Creating a table with a CHECK constraint to ensure that the 'Age' column only accepts values greater than 18:

CREATE TABLE Employees (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    Age NUMBER CHECK (Age > 18)
);