Constraints in Oracle SQL

In Oracle SQL, constraints are rules enforced on table columns to ensure data integrity and accuracy. Here are the main types of constraints, explained with simple examples:

1. NOT NULL Constraint

Ensures that a column cannot have a NULL value.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL
);

Here, the emp_name column must have a value; it cannot be left empty.

1. UNIQUE Constraint

Ensures all values in a column (or combination of columns) are unique, meaning no duplicate values.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_email VARCHAR2(100) UNIQUE
);

Here, each employee’s emp_email must be unique across the table.

1. PRIMARY KEY Constraint

A combination of NOT NULL and UNIQUE—it uniquely identifies each row in a table.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL
);

Here, emp_id is the primary key, meaning each emp_id is unique and must not be null.

1. FOREIGN KEY Constraint

Establishes a link between two tables. It ensures that the value in a column (or a set of columns) matches values in a column of another table.

CREATE TABLE Departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50)
);

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept_id NUMBER,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Here, dept_id in Employees references dept_id in Departments, ensuring each dept_id in Employees exists in Departments.

1. CHECK Constraint

Ensures that values in a column meet a specified condition.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
salary NUMBER CHECK (salary > 0)
);

Here, the salary column must contain positive numbers (greater than 0).

1. DEFAULT Constraint

Assigns a default value to a column when no value is provided.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
join_date DATE DEFAULT SYSDATE
);

Here, if join_date is not specified during insertion, it will automatically take the current date (SYSDATE).

These constraints ensure data integrity and enforce rules that keep the data consistent and accurate across your database.