What are the various types of constraints in Oracle, and how do they ensure data integrity?

In Oracle database, constraints are used to define rules that ensure the quality and correctness of the data stored in tables. The following are the various types of constraints:

1. NOT NULL Constraint
The NOT NULL constraint ensures that a column cannot contain any NULL value. It can be applied to any data type except for LONG and LOB data types.

Example:

CREATE TABLE employees (
    emp_id NUMBER(10),
    emp_name VARCHAR2(50) NOT NULL,
    emp_email VARCHAR2(50) NOT NULL
);

2. UNIQUE Constraint The UNIQUE constraint ensures that each value in a column is unique. A table can have more than one unique constraint, and each constraint can be composed of more than one column.

Example:

CREATE TABLE departments (
    dept_id NUMBER(3) UNIQUE,
    dept_name VARCHAR2(50) UNIQUE
);

3. PRIMARY KEY Constraint The PRIMARY KEY constraint ensures that each record in a table is uniquely identified by a particular column or group of columns. It is a combination of a UNIQUE constraint and a NOT NULL constraint.

Example:

CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_email VARCHAR2(50)
);

4. FOREIGN KEY Constraint The FOREIGN KEY constraint is used to establish a relationship between two tables. It ensures that the data in the referencing column or columns of a table matches the data in the referenced column or columns of another table.

Example:

CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(50),
    dept_id NUMBER(3),
    CONSTRAINT fk_dept FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
);

5. CHECK Constraint The CHECK constraint is used to ensure that the data in a column meets a certain condition. The condition can be a logical expression or a subquery that returns a Boolean value.

Example:

CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(50),
    emp_age NUMBER(2) CHECK (emp_age >= 18 AND emp_age <= 65),
    emp_salary NUMBER(10,2) CHECK (emp_salary > 0)
);

These constraints ensure data integrity by preventing the insertion of invalid or inconsistent data into tables. They help maintain data accuracy, consistency, and reliability, and prevent data corruption or loss.