SQL CONSTRAINT Keyword

 In SQL, the CONSTRAINT keyword is used to define rules or restrictions on the data in a table. Constraints help maintain data integrity by ensuring that the data entered into the database adheres to certain conditions. Constraints can be applied to columns or entire tables. There are different types of constraints, and they can be categorized as follows:

1. NOT NULL

Ensures that a column cannot have a NULL value.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL
);

2. UNIQUE

Ensures that all values in a column are unique across the rows.

CREATE TABLE employees (
    employee_id INT UNIQUE,
    email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY

A combination of NOT NULL and UNIQUE constraints. It uniquely identifies each record in the table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

4. FOREIGN KEY

Ensures that the value in one table matches a value in another table, enforcing referential integrity.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

5. CHECK

Ensures that the values in a column satisfy a specific condition or expression.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10, 2),
    CHECK (salary > 0)
);

6. DEFAULT

Provides a default value for a column when no value is specified.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    hire_date DATE DEFAULT CURRENT_DATE
);

7. INDEX

Though not strictly a constraint, an index can be considered a performance-enhancing constraint for improving the speed of data retrieval.

CREATE INDEX idx_employee_id ON employees(employee_id);

Example of Using Constraints Together:

CREATE TABLE employees (
    employee_id INT NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    hire_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (employee_id)
);

In this example:

• employee_id cannot be NULL and is the primary key.
• email must be unique.
• salary must be greater than zero.
• hire_date will default to the current date if not specified.