Text copied!
SQL Constraints
SQL constraint is a rule applied to the 'column(s)', that restricts the values that can be entered into that column(s).
Constraints can be defined during the table creation by using "CREATE TABLE statement" or added to an existing table using "ALTER TABLE statement". They remain in place until explicitly removed or modified.
Here's an example of SQL constraints :
CREATE TABLE Employee (
[Id] INT PRIMARY KEY,
[First_Name] VARCHAR(50) NOT NULL,
[Last_Name] VARCHAR(50) NOT NULL,
[Email] VARCHAR(255) UNIQUE,
[Age] INT CHECK (age >= 18),
[Salary] DECIMAL(10,2) DEFAULT 0,
[Department_Id] INT REFERENCES Department(id) ON DELETE CASCADE
);
Common types of SQL constraints include :
PRIMARY KEY :
PRIMARY KEY constraint on [Id] column to ensure that each employee has a unique ID.
NOT NULL :
NOT NULL constraints on the [FirstName] and [LastName] columns to ensure that these fields cannot be left empty.
UNIQUE :
UNIQUE constraint on the [Email] column to ensure that each email address is unique.
CHECK :
CHECK constraint on the [Age] column to ensure that employee are at least 18 years old.
DEFAULT :
DEFAULT constraint on the [Salary] column to set the default value to the [Salary] if no value is specified during an insert operation.
FOREIGN KEY :
FOREIGN KEY constraint is used to establish a relationship between two tables. The FOREIGN KEY constraint is added to a column in one table that references the PRIMARY KEY column of another table. This constraint ensures that the values in the referencing column must match the values in the referenced column or be null. It can also enable the use of cascading updates and deletes, which can automatically update or delete related records in the referenced table when a record in the referencing table is modified or deleted.