What are constraints in SQL?

SQL constraints are rules applied to columns in a table to restrict the data types that can be stored in them. They ensure data integrity, accuracy, and reliability.

Main types of constraints:

• NOT NULL: Ensures that a column cannot contain NULL values.
• UNIQUE: Ensures all values in a column are unique.
• PRIMARY KEY: A combination of UNIQUE and NOT NULL. Defines a unique record in the table.
• FOREIGN KEY: Links data between two tables by referencing the PRIMARY KEY in another table. Ensures referential integrity.
• DEFAULT: Sets a default value for a column if no other value is specified during insertion.
• CHECK: Restricts the range of values that can be stored in a column.

Example of creating a table with constraints:

CREATE TABLE Persons (
    PersonID int NOT NULL UNIQUE, -- NOT NULL and UNIQUE
    LastName varchar(255) NOT NULL, -- NOT NULL
    FirstName varchar(255),
    Age int CHECK (Age >= 18), -- CHECK
    City varchar(255) DEFAULT 'London', -- DEFAULT
    PRIMARY KEY (PersonID) -- PRIMARY KEY
);

Example of creating a FOREIGN KEY:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) -- FOREIGN KEY
);

Constraints can be added during table creation or modified afterward.

Constraint	Description
NOT NULL	Disallows NULL values
UNIQUE	All values are unique
PRIMARY KEY	Unique identifier for a row (NOT NULL + UNIQUE)
FOREIGN KEY	References the PRIMARY KEY of another table (referential integrity)
DEFAULT	Default value
CHECK	Validates conditions for values