What are constraints in SQL?

SQL constraints are rules applied to columns or tables to ensure data integrity. They define the set of permissible data in a table.

Main types of constraints:

• NOT NULL: The column cannot contain null values.
• UNIQUE: All values in the column must be unique.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE constraints. It uniquely identifies each record in the table. A table can have only one primary key.
• FOREIGN KEY: Establishes a link between two tables by referencing the primary key or a unique key of another table. It ensures referential integrity.
• CHECK: Checks that all values in the column satisfy a specific condition.
• DEFAULT: Sets a default value for the column if no value is provided during record insertion.

Example of creating a table with constraints:

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY, -- Primary key
    first_name VARCHAR(50) NOT NULL, -- Cannot be NULL
    last_name VARCHAR(50) NOT NULL, -- Cannot be NULL
    email VARCHAR(100) UNIQUE, -- Unique value
    hire_date DATE DEFAULT CURRENT_DATE, -- Default value (current date)
    salary DECIMAL(10, 2) CHECK (salary > 0) -- Condition: salary must be greater than 0
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    order_date DATE,
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) -- Foreign key referencing Employees table
);

Constraints help maintain data accuracy and reliability in the database.