MSDN – SQL Server T‑SQL

Overview

Constraints enforce data integrity and business rules at the table level. SQL Server supports several constraint types that can be defined when a table is created or altered.

Constraint Types

CHECK

Validates that a column or a set of columns satisfies a logical expression.

DEFAULT

Provides a default value for a column when an INSERT statement omits the column.

FOREIGN KEY

Ensures referential integrity between related tables.

PRIMARY KEY

Uniquely identifies each row in a table and creates an implicit unique index.

UNIQUE

Guarantees that all values in a column (or combination of columns) are distinct.

Syntax

CONSTRAINT constraint_name
    { CHECK (logical_expression)
    | DEFAULT default_value
    | FOREIGN KEY (column_list) REFERENCES referenced_table (ref_column_list)
      [ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]
      [ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]
    | PRIMARY KEY [CLUSTERED | NONCLUSTERED] (column_list) [WITH (options)]
    | UNIQUE [CLUSTERED | NONCLUSTERED] (column_list) [WITH (options)] }

Examples

Creating a CHECK constraint

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    Salary     money,
    CONSTRAINT CK_Employees_Salary CHECK (Salary >= 0)
);

Adding a DEFAULT constraint

ALTER TABLE Employees
ADD CONSTRAINT DF_Employees_StartDate
DEFAULT (GETDATE()) FOR StartDate;

Defining a FOREIGN KEY

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    EmployeeID int,
    CONSTRAINT FK_Orders_Employee
        FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
        ON DELETE SET NULL
);

Creating a UNIQUE constraint

CREATE TABLE Departments (
    DeptID int PRIMARY KEY,
    DeptName nvarchar(50) NOT NULL,
    CONSTRAINT UQ_Departments_Name UNIQUE (DeptName)
);