All Constraints in SQL Server

Last month we discussed quirky check constraint on SQL Server. That made me think of all constraints in SQL Server.

What is a constraints

Before we actually see all the constraint types in SQL Server, let us find out what is a constraint in SQL Server and why it is there.

Constraints are basically guard rails in database to prevent invalid, inconsistent and meaning data in tables. It is a rule SQL Server enforces automatically on a table column or set of columns.

Instead of trusting database users or applications, SQL server itself can enforce these rules to make sure data is protected.

Example for database rules are:

• A person should not have two identical NIC (social security) numbers
• An order should always belong to a customer
• Age should never be negative

Constraints not only help to keep data in check, but it also help SQL Server Optimizer to arrive at better execution plans. For example, if optimizer knows data in a column cannot be null, it can create a plan which was otherwise not possible.

01. Primary Key Constraint

I think most important of all constraint is primary key constrint. Basic use of Primary key is identifying a specific row in a table. It is actually a combination of NOT NULL and UNIQUE constraints, which means you cannot have null on Primary Key columns and each value need to be unique. Otherwise SQL server cannot identify the row.

One table can have only one Primary Key constraint.

Primary key can be defined two ways. You can specify it as constraint on it's own line (like below).

CREATE TABLE Customers (

    CustomerId   INT           NOT NULL,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)

);

or you can specify against the column. But in this case, you cannot specify a name for the constraint.

CREATE TABLE Customers (

    CustomerId   INT PRIMARY KEY,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)

);

When you add a primary key to a table, that table is physically restructured to sort on specified primary key column values (default behaviour, but can be changed).

You can have composite primary keys. Which means you can define a primary key on more than one column.

CREATE TABLE Customers (

    Surname NVARCHAR(100)  NOT NULL,

    Email        NVARCHAR(255) NOT NULL,

    FullName     NVARCHAR(255) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (Surname, Email)

);

Experts recommend to have primary key in every table.

02. Foreign Key Constraint

Foreign key constraint create relationship between two tables. This rule make sure, value user enter into the column actually exists in the foreign table.

For example, let us consider Order table.

CREATE TABLE Orders (

    OrderId    INT  NOT NULL,

    CustomerId INT  NOT NULL,

    OrderDate  DATE NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),

    CONSTRAINT FK_Orders_Customers

        FOREIGN KEY (CustomerId)

        REFERENCES Customers (CustomerId)

);

In above example, CustomerId column in the Orders table reference (or have a relationship) with CustomerId field in Customers table. Which means, user cannot enter a customer id which doesn't exists in the Customers table into Orders table. This make sure orphan or invalid data is not entered into Orders table.

You can have self referencing Foreign keys. For example, see below Employees table:

CREATE TABLE Employees (

    EmployeeId INT           NOT NULL,

    FullName   NVARCHAR(255) NOT NULL,

    ManagerId  INT           NULL,  -- NULL means "this person has no manager" (the CEO, basically)

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),

    CONSTRAINT FK_Employees_Manager

        FOREIGN KEY (ManagerId)

        REFERENCES Employees (EmployeeId)  -- references the same table!

);

In here, "ManagerId" column has self-referencing relationship with EmployeeId column in same table. Basically, manager id is employee id of the manager of that particular employee.

As an additional feature, you can specify, what action to take when referenced column is updated or deleted.

CONSTRAINT FK_Orders_Customers

    FOREIGN KEY (CustomerId)

    REFERENCES Customers (CustomerId)

    ON DELETE CASCADE   -- delete the order if the customer is deleted

    ON UPDATE NO ACTION -- (default) block updates that would break the link

In above example if a customer is deleted in Custoemrs table, all orders reference by that customer id, will be also deleted.

It is recommend to use these actions carefully as it can create un-intended behaviours.

You can have foreign key constraint on multiple columns - Composit Foreign key. 

For example, consider following scenario:

CREATE TABLE OrderItems (

    OrderId   INT            NOT NULL,

    ProductId INT            NOT NULL,

    Quantity  INT            NOT NULL,

    UnitPrice DECIMAL(10, 2) NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderId, ProductId),  -- composite PK

);

CREATE TABLE OrderItemNotes (

    NoteId    INT            NOT NULL,

    OrderId   INT            NOT NULL,

    ProductId INT            NOT NULL,

    Note      NVARCHAR(1000) NOT NULL,

    CONSTRAINT PK_OrderItemNotes PRIMARY KEY (NoteId),

    CONSTRAINT FK_OrderItemNotes_OrderItems

        FOREIGN KEY (OrderId, ProductId)               -- both columns together...

        REFERENCES OrderItems (OrderId, ProductId)     -- ...must exist as a pair

);

In above example, OrderId and ProductId is the primary key of the OrderItems tables. Then those two columns were referenced in OrderItemNotes table with a foreign key relationship. Important thing is combination need to be unique, therefore, you need Primary key or unique constraint on parent table on those columns you reference in child table.

However, note that two columns need to be in same table.

When referencing columns, child table doesn't have to have same name for the columns. But need to have compatible data types and need to match the order.

03. Unique Constraint

Keep all values in a column unique and no duplicates are allowed. For example, let us consider Email column in Customers table.

ALTER TABLE Customers

ADD CONSTRAINT UQ_Customers_Email UNIQUE (Email);

Above constraint make sure no two customers have same email address.

Unlike primary key, you can have multiple unique constraints defined on a table.

Unique key constraint considers NULL as a value and allow 1 null value (no duplicate nulls though). You cannot have filtered unique constraints; however, you can create unique index which makes the same functionality (we will not discuss about indexes here).

Like primary keys, you can have unique constraint on multiple columns. Which means uniqueness is checked across columns.

-- A customer can't place the same order twice on the same day

ALTER TABLE Orders

ADD CONSTRAINT UQ_Orders_CustomerDate UNIQUE (CustomerId, OrderDate);

04. Not NULL Constraint

Make sure column always have a value (no null values are allowed). This check is enforced every time row is inserted or updated.

CREATE TABLE Products (

    ProductId   INT            NOT NULL,

    ProductName NVARCHAR(255)  NOT NULL,  -- can never be empty

    Description NVARCHAR(MAX)  NULL,      -- optional

    Price       DECIMAL(10, 2) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId)

);

In above example, ProductName cannot be null, always need to have a value. But in contrast, Description can be null. Also note Primary key constraints automatically enforce not null.

05. Default Constraint

This constraint makes sure there is always a value in the specified column, even when user didn't insert a value.

CREATE TABLE Orders (

    OrderId    INT          NOT NULL,

    CustomerId INT          NOT NULL,

    OrderDate  DATE         NOT NULL,

    Status     NVARCHAR(50) NULL CONSTRAINT DF_Orders_Status DEFAULT 'Pending',

    CreatedAt  DATETIME2    NOT NULL CONSTRAINT DF_Orders_CreatedAt DEFAULT SYSDATETIME(),

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId)

);

In above example, if user didn't specify a value to Status column, value "Pending" is get inserted. However, if user specify "NULL" value to Status field explicitly in the statement, NULL will be set for that column. Default value only applied when statement (insert or update) doesn't explicitly specify a value. To avoid this, you can have NOT NULL constraint on columns with default values.

06. Check Constraint

Check constraint allow you do define a custom rule using any expression that evaluate to true or false. If the expression evaluates to false for a given row, the insert or update will fail for that row.

ALTER TABLE Products

ADD CONSTRAINT CHK_Products_Price CHECK (Price > 0);

In above example, check constraint make sure, users cannot insert or update a row where Price is less than or equal to 0.

You can have check constraints on multiple columns:

-- A multi-column check: end date must be after start date

ALTER TABLE Promotions

ADD CONSTRAINT CHK_Promotions_DateRange

    CHECK (EndDate > StartDate);

However, if condition evaluate to NULL, then condition will pass the check. To avoid this you can pair the check constraint with NOT NULL.

Disabling Constraints

You can disable Foreign key constraints and Check constraints temporarily. This tick is used when loading data to a table fast (bulk load).

-- Disable a constraint

ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers;

-- Re-enable (and verify existing data)

ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

Note that "WITH CHECK" tells SQL server to validate all existing rows. So if validation fails for any of the rows, constraints will not be enabled.

You can bypass this by specifying WITH NOCHECK, but this is not recommended.

If constraints are re-enabled with NOCHECK, they are marked as NOT TRUSTED. Which means those constraints will not be used in query optimization or in execution plan building (i.e. SQL server will not be believing data is valid).