SQL Constraints Reference

This document provides a comprehensive reference to the various constraints you can define in SQL Server to enforce data integrity.

What are SQL Constraints?

Constraints are rules enforced on data columns in a table. They are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. If there is any violation between the constraint and the data action, the action is automatically aborted.

Types of Constraints

UNIQUE Constraint

Ensures that all values in a column are different.

Can contain only NULL values. If a column is of INT type, then the normal range of values is not affected. However, all the values must be unique.

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
                

To allow NULL values in a unique column, you can define the constraint like this:

CREATE TABLE Persons (
    ID int NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
                

PRIMARY KEY Constraint

Uniquely identifies each record in a table. A table can have only one primary key.

The PRIMARY KEY constraint uniquely identifies each record in a table.

It also helps to have a primary key, because records can be easily found and accessed.

A PRIMARY KEY constraint ensures that:

Each value is unique.
Each value is NOT NULL.

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);
                

To define a composite primary key (multiple columns):

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderDate date NOT NULL,
    CustomerID int NOT NULL,
    PRIMARY KEY (OrderID, OrderDate)
);
                

FOREIGN KEY Constraint

Links two tables together. It is a field (or collection of fields) in one table, that uniquely identifies a row of another table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustomerID int,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
                

Consider the following two tables:

-- Customers Table
CREATE TABLE Customers (
    CustomerID int NOT NULL PRIMARY KEY,
    CustomerName varchar(255) NOT NULL,
    ContactName varchar(255)
);

-- Orders Table
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustomerID int,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
                

CHECK Constraint

Restricts the range of values that can be placed in a column.

If you define a CHECK constraint on a single column, it allows only certain values for that column.

You can also define a CHECK constraint on multiple columns.

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age >= 18)
);
                

To define a check constraint with multiple conditions:

CREATE TABLE Products (
    ProductID int NOT NULL PRIMARY KEY,
    ProductName varchar(255) NOT NULL,
    Price money CHECK (Price > 0 AND Price < 5000)
);
                

DEFAULT Constraint

Inserts a default value into a column if no other value is specified.

The DEFAULT constraint is used to set a default value for a column when no value is specified.

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int DEFAULT 18
);
                

If you insert a new record without specifying the Age column, it will automatically be set to 18.

NOT NULL Constraint

Ensures that a column cannot have a NULL value.

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255)
);
                

Managing Constraints

You can add or drop constraints from existing tables using ALTER TABLE statements.

Adding a Constraint

To add a UNIQUE constraint to the "Email" column of the "Customers" table:

ALTER TABLE Customers
ADD CONSTRAINT UC_Email UNIQUE (Email);
            

Dropping a Constraint

To drop a PRIMARY KEY constraint:

ALTER TABLE Orders
DROP CONSTRAINT PK__Orders__3A81B3277C0F6B3A; -- Replace with the actual constraint name
            

Naming Constraints

It is good practice to explicitly name your constraints for easier management and troubleshooting.

ALTER TABLE Persons
ADD CONSTRAINT CK_AgeCheck CHECK (Age >= 18 AND Age <= 100);
            