Create SQL Constraints - Null, Check, Default, Unique, Primary Key, Foreign Key

By: Joe Gavin   |   Updated: 2023-02-08   |   Comments (2)   |   Related: > Constraints

Problem

What is a SQL constraint? How do you create SQL constraints? How do SQL constraints help a Microsoft SQL Server database application?

Solution

A constraint is a rule in a relational database that you create to make sure that data stored in a table adheres to specific standards that you set, such as always making sure there is a value in a particular column, or that the data in a column has to be unique from all other values that are stored.

In this SQL tutorial, we'll demonstrate examples for the types of constraints that can be created in Microsoft SQL Server.  The T-SQL constraint code can be copied, pasted, and modified for your needs.

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint

First, we'll create a database called MyDatabase to use for the examples.  Here is the syntax:

USE master;
GO
 
CREATE DATABASE [MyDatabase];
GO

Not Null Constraint

A Not Null Constraint ensures that a column cannot have a null value, which is generally considered a column level constraint.

T-SQL syntax to create a database table called Employees with three fields that all allow nulls.

USE [MyDatabase]
GO
 
CREATE TABLE [dbo].[Employees]( -- Create Table Statement
   [FirstName]   [nvarchar](40) NULL, -- data types
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date]         NULL
);
GO

Insert two records: 1) has a value for each field, and 2) has no value for DateOfBirth.

INSERT INTO [dbo].[Employees] ( [FirstName], [LastName], [DateOfBirth] )
VALUES ('John', 'Doe', '2000-04-01');
 
INSERT INTO [dbo].[Employees] ( [FirstName], [LastName] )
VALUES ('Robert', 'Smith')

We can see our two records; the DateOfBirth field is null for the second record with the following SQL query:

SELECT [FirstName],
       [LastName],
       [DateOfBirth]
FROM [dbo].[Employees];

T-SQL syntax to create a second database table called Emloyees_2, but this time, don't allow nulls on the DateOfBirth column. Attempt the same two insert statements.

CREATE TABLE [dbo].[Employees_2](
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date]         NOT NULL
);
GO
 
INSERT INTO [dbo].[Employees_2] ( [FirstName], [LastName], [DateOfBirth] )
VALUES ('John', 'Doe', '2000-04-01');
 
INSERT INTO [dbo].[Employees_2] ( [FirstName], [LastName] )
VALUES ('Robert', 'Smith');

The first insert worked, but the second SQL command generated this error:

And we see that only the first record was inserted with this SQL query:

SELECT [FirstName],
       [LastName],
       [DateOfBirth]
FROM [dbo].[Employees_2];

Check Constraint

A Check Constraint limits the values that can be stored in a column.

We'll create a new table called Employees_3. It's safe to assume that anyone we hire would be less than 100 years old at the time of hire. We can add a Check Constraint on DateOfBirth and use the DATEADD function to calculate the date 100 years ago with the following SQL statement:

USE [MyDatabase];
GO
 
CREATE TABLE [dbo].[Employees_3](
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date]         NOT NULL,
   CHECK ([DateOfBirth] >= DATEADD(year,-100,GETDATE()))
);
GO

We can insert a record where the DateOfBirth of 1990-03-12 is less than 100 years ago, so the check passes.

INSERT INTO [dbo].[Employees_3] ( [FirstName], [LastName], [DateOfBirth] )
VALUES ('Jane', 'Smith', '1990-03-12');

But what happens if we try to enter the date as 1995-02-01 but accidentally insert it as 1885-02-01?

INSERT INTO [dbo].[Employees_3] ( [FirstName], [LastName], [DateOfBirth] )
VALUES ('John', 'Smith', '1885-02-01');

The check fails, and we get:

Default Constraint

A Default Constraint will put a default value in a column if it is not specified, unlike a Not Null Constraint which will not allow the operation.

Creating the table with DEFAULT '1900-01-01' will populate the DateOfBirth field with the default if no value is entered.

CREATE TABLE [dbo].[Employees_4](
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date]         DEFAULT '1900-01-01'
);
GO

Insert values for just the FirstName and Lastname fields.

INSERT INTO [dbo].[Employees_4] ( [FirstName], [LastName] )
VALUES ('Mary', 'Anderson');
GO

Select all fields from the table and see DateOfBirth = '1900-01-01'.

SELECT [FirstName],
       [LastName],
       [DateOfBirth]
FROM [dbo].[Employees_4];
GO

Unique Constraint

A Unique Constraint will not allow a duplicate value in a column.

We will an additional field called EmpNum, which is used as a unique identifier.

CREATE TABLE [dbo].[Employees_5](
   [EmpNum]      [int]          NOT NULL UNIQUE,
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date]
);
GO

Records are inserted with unique EmpNum values.

INSERT INTO [dbo].[Employees_5] ( [EmpNum], [FirstName], [LastName], [DateOfBirth] )
VALUES (100,'Louise', 'Smith','1982-04-01');
 
INSERT INTO [dbo].[Employees_5] ( [EmpNum], [FirstName], [LastName], [DateOfBirth] )
VALUES (101,'Tom', 'Cooper','1982-04-01');

What happens if we try to insert a row with an EmpNum already used?

INSERT INTO [dbo].[Employees_5] ( [EmpNum], [FirstName], [LastName], [DateOfBirth] )
VALUES (101,'William', 'Jones','1972-12-25');

An error is generated telling us a non-unique value cannot be entered:

Primary Key Constraint

A Primary Key Constraint will not allow a duplicate value or NULL in a column in order to enforce referential integrity.

Create a table with a Constraint called PK_EmpNum on the EmpNum field.

CREATE TABLE [dbo].[Employees_6](
   [EmpNum]      [int]          NOT NULL,
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date],
   CONSTRAINT PK_EmpNum PRIMARY KEY ([EmpNum]) -- Primary Key Column
);
GO

Insert a row with a unique EmpNum.

INSERT INTO [dbo].[Employees_6] ( [EmpNum], [FirstName], [LastName], [DateOfBirth] )
VALUES (100,'Jennifer', 'Jones','1985-07-05');
GO

Now, attempt to insert a row without an EmpNum.

INSERT INTO [dbo].[Employees_6] ( [FirstName], [LastName], [DateOfBirth] )
VALUES ('Jennifer', 'Jones','1985-07-05');
GO

And this error is generated:

And here, we'll attempt to insert a row with an EmpNum that's already in the table.

INSERT INTO [dbo].[Employees_6] ( [EmpNum], [FirstName], [LastName], [DateOfBirth] )
VALUES (100,'Bill', 'Smith','1965-11-05');
GO

And we get this error:

Foreign Key Constraint

A Foreign Key Constraint is a Primary Key in another table. Its purpose is to prevent breaking referential integrity between tables.

Create a table called Departments with a Primary Key on DepId.

CREATE TABLE [dbo].[Departments](
   [DeptId]   [int]          NOT NULL,
   [DeptName] [nvarchar](40) NOT NULL,
   CONSTRAINT PK_DeptId PRIMARY KEY  ([DeptId]) -- Primary Key Column
);
GO

Insert some records into the table.

INSERT INTO [dbo].[Departments] ( [DeptId], [DeptName] )
VALUES (1,'Accounting'),
       (2, 'HR'),
       (3, 'IT');

Next, create a table called Employees_7 with a Foreign Key reference to DeptId in Departments.

CREATE TABLE [dbo].[Employees_7](
   [EmpNum]      [int]          NOT NULL,
   [FirstName]   [nvarchar](40) NULL,
   [LastName]    [nvarchar](40) NULL,
   [DateOfBirth] [date],
   [DeptId]      [int],
   CONSTRAINT FK_DeptId FOREIGN KEY ([DeptId]) REFERENCES [Departments]([DeptId]) -- Foreign Key Column
);
GO

Insert records into Employees_7 if the DeptId exists in Departments.

INSERT INTO [dbo].[Employees_7] ( [EmpNum], [FirstName], [LastName], [DateOfBirth], [DeptId] )
VALUES (100,'Jennifer', 'Jones','1985-07-05',1),
       (101,'Alan', 'Smith','1980-08-05',2),
       (101,'Joe', 'Lee','1981-09-09',3);

What if we try to insert a record with a DeptId that doesn't exist in Departments?

INSERT INTO [dbo].[Employees_7] ( [EmpNum], [FirstName], [LastName], [DateOfBirth], [DeptId] )
VALUES (100,'Jim', 'Louis','1975-01-28',4);

The insert fails, and we get this error:

Next Steps

Here are links to more tips on DBMS constraints:

• Difference between SQL Server Unique Indexes and Unique Constraints
• Enforce a Unique Constraint Where Order Does Not Matter in SQL Server
• Should I Use a Unique Constraint or a Unique Index in SQL Server?
• Checking for Potential Constraint Violations Before Entering SQL Server TRY and CATCH Logic
• Finding and Fixing SQL Server Database Constraint Issues
• Working with Default Constraints in SQL Server
• Drop and Re-Create All Foreign Key Constraints in SQL Server
• Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server Database Using T-SQL
• SQL Server Unique Constraints for Large Text Columns
• Avoid Untrusted Constraints in SQL Server

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips