Defining SQL Server constraints with TSQL

by Tim Chapman | May 07, 2007 3:14:00 PM

Tags: Databases, server, Tim Chapman, Microsoft SQL Server, SalesHistory, SQL Server Newsletter

Takeaway: Take your database administration skills to the next level by learning how to define constraints with TSQL. This advanced skill shows that you don't always rely on graphical interfaces when designing database objects.

All competent DBAs and developers should possess the ability to logically define constraints in the database, as well as define those constraints via TSQL code. This article explains how to define constraints with TSQL and explores different ways to define these constraints via TSQL.

Types of constraints

I focus on four types of constraints: primary key, foreign key, unique, and check. Here's a brief overview of each.

Primary key
This constraint is used to guarantee that a column or set of columns on a table contain unique values for every record in the given table. This lets you ensure data integrity by always being able to uniquely identify the record in the table.

A table can have only one primary key constraint defined on it, and the rows in the primary key columns cannot contain null values. A primary key constraint can be defined when a table is created, or it can be added later.

This script creates a primary key constraint on a single field when the table is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL
)

GO

The followings script creates the primary key constraint when the table is created. This method allows you to define a name for the constraint and to create the constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO
CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] IDENTITY(1,1) NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL, 
      CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)

)

GO

This script creates the primary key constraint on the table after it is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO



CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] IDENTITY(1,1) NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL

)

GO

ALTER TABLE SalesHistory

ADD CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)

GO

Foreign key
This constraint limits the values of columns in one table based upon the values of columns in another table. This link between the two tables requires the use of a "lookup table," which contains the accepted list of values; this list must contain a unique or primary key constraint. After the constraint is established between the two tables, any data modifications to the fields defined in the constraint on the foreign key table will cause a validation to ensure that the data being updated or inserted is contained in the lookup table.

The script in Listing A creates a ProductTypes table, which will serve as the lookup table and the SalesHistory table, which will reference the ProductID in the ProductTypes table. If I had excluded the constraint definition in the table declaration, I could go back later and add it. You can do this with the script in Listing B.

The previous script contains the WITH NOCHECK clause. I use it so that any existing values in the table are not considered when the constraint is added. Any records in the table that violate the newly added constraint will be ignored so that the constraint is created. The constraint will only be applicable to new records entered into the SalesHistory table.

Unique
This constraint guarantees that the values in a column or set of columns are unique. Unique and primary key constraints are somewhat similar because each provide a guarantee for uniqueness for a column or set of columns. A primary key constraint automatically has a unique constraint defined on it.

There are two differences between the constraints: (1) You may have only one primary key constraint per table, yet you may have many unique constraints per table; (2) A primary key constraint will not allow null values but a unique constraint will (although it will only allow one null value per field).

This script creates a unique constraint on the SaleID column when the table is created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO
CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] NOT NULL UNIQUE,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL

)

GO

The following script creates a unique constraint on the table at creation, and it allows for constraint naming and for defining the unique constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int]  NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL, 
      CONSTRAINT uc_SaleID UNIQUE (SaleID)

)

GO

This script creates the unique constraint on the SalesHistory table by altering the table after it has been created:

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int] NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL

)

GO

ALTER TABLE SalesHistory

ADD CONSTRAINT uc_SaleID UNIQUE(SaleID)

GO

Check
This constraint limits the value range, or domain, in a column. Check constraints check the acceptable values against a logical expression defined in the constraint. These constraints are similar to foreign key constraints in that they both govern the acceptable values for a column or set of columns in a given row in a table. You can create a check constraint at the column or table level. A check constraint on a single column allows only certain values for those columns, while a table check constraint can limit values in certain columns based on values in other fields in the row.

The following script creates a check constraint on the SalePrice column in the SalesHistory table, limiting entries where the SalePrice must be greater than 4. Any attempt to enter a record with the SalePrice present and less than 4 will result in an error.

IF OBJECT_ID('SalesHistory')>0
      DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory](
      [SaleID] [int]  NOT NULL,
      [Product] [char](150) NULL,
      [SaleDate] [datetime] NULL,
      [SalePrice] [money] NULL CHECK (SalePrice > 4)

)

GO

The script in Listing C creates a check constraint on the SalesHistory table, limiting the SalePrice to be greater than 10 and the Product field to have the value Computer. This isn't a very practical constraint, but it does illustrate how you can set constraints on multiple columns for a table. Listing D is effectively the same as the script in Listing C, but it defines the constraint after the table is created.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.