Introduction to SQL Server Constraints

Mike Chapple

SQL Server constraints allow you to enforce rules in your database. These rules may affect business logic, database integrity and/or table structures. Each one plays an important role in your database architecture.

Server Constraints Overview

The six types of constraints supported by Microsoft SQL Server include:

  • UNIQUE constraints allow SQL Server administrators to specify that a column may not contain duplicate values. When you create a new UNIQUE constraint, SQL Server checks the column in question to determine whether it contains any duplicate values. If the table contains pre-existing duplicates, the constraint creation command fails. Similarly, once you have a UNIQUE constraint on a column, attempts to add or modify data that would cause duplicates to exist also fail.
  • CHECK constraints allow you to limit the types of data that users may insert in a database. They go beyond data types and allow you to define the specific values that may be included in a column.
  • DEFAULT constraints allow you to specify a value that the database will use to populate fields that are left blank in the input source. They're a replacement for the use of NULL values that provide a great way to predefine common data elements.
  • NOT NULL constraints allow you to specify that a column may not contain NULL values. When you create a new NOT NULL constraint on a database column, SQL Server checks the column's current contents for any NULL values. If the column currently contains NULL values, the constraint creation fails. Otherwise, SQL Server adds the NOT NULL constraint and any future INSERT or UPDATE commands that would cause the existence of a NULL value fail.
  • PRIMARY KEY constraints specify fields that uniquely identify each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys may consist of a single attribute or multiple attributes in combination.
  • FOREIGN KEY constraints are fields in a relational database table that match the primary key column of another table. Foreign keys can be used to cross-reference tables.

Each of these constraints plays a significant, unique role when creating a SQL Server database architecture.