SQL Server Constraints Introduction

 in SQL Server  tagged validation / constraints / unique / null / key / transaction / primary / surrogate by

SQL Server has several types of constraints. But what is a constraint? Why do you need them? Which type of constraint should I use? This series will address these questions.

Microsoft Books Online says of Constraints: “Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules, and defaults.”

SQL Server supports the following types of constraints.

  • NOT NULL
  • CHECK constraints
  • UNIQUE constraints
  • PRIMARY KEY constraints
  • FOREIGN KEY constraints

Constraint Naming

SQL Server insists that constraint names be unique withing a database. Even though constraints apply to tables, the names must be unique in the entire database.

Unique Constraints

UNIQUE constraints enforce the uniqueness of the values in a set of columns. In a UNIQUE constraint, no two rows in the table can have the same value for the columns. Primary keys also enforce uniqueness, but primary keys do not allow for NULL as one of the unique values.

You would very rarely use this on null-able columns. Normally you use it on NOT NULL columns. Once you have successfully added your unique constraint, you will find it in Object Explorer under the Keys folder, not under the Constraints folder as you might expect. It also creates an index and that will appear under the Index folder in Object Explorer.

Unique Constraints on Multiple Columns

Suppose we were to add a unique constraint to three columns at once. We would use the following syntax (as an example). 

alter table Tbl2
add constraint unqTbl2 unique (Amount, DataOfTransaction, EmployeeNumber)

Think of the combination of all three columns being unique. For example, it would be fine if two different employees had the same Amount on the same day. It is also okay if two different Amounts were entered on the same day by the same employee. It is also acceptable if on two different days the same employee had the same amount. However, the same employee cannot have the same amount on the same day, as that would be a duplication.

Drop Constraint

You can delete a constraint using drop constraint. 

alter table Tbl2
drop constraint unqTbl2

Statement Terminated

Let’s imagine we are insert one million rows all at once into a Tbl2. If even one of those rows violates the unique key constraint unqTbl2, none of the rows will be inserted. The entire statement is terminated.

Testing Statements using Transactions

You can enclose your statements in a transaction to test if they will pass or fail. 

begin tran
-- your statement here
rollback tran

Once you are confident with the results, you can just change the word rollback to commit and run it again. This is particularly useful when you are deleting records with a where clause. Then you can run a select right after that to observe the effects. Then you can roll it back. Transactions are discussed in the post called SQL Server Exception Handling Part 2.

Modify Constraints

You cannot modify a constraint in T-SQL. You need to drop it and then create it.

Default Constraints

Default constraints actually don’t really constrain anything, so perhaps they are misnomers. A default constraint is a replacement for NULL at the time of creating the row. Defaults specify what values are used in a column if you do not specify a value for the column when you insert a row. Defaults can be anything that evaluates to a constant, such as a constant, built-in function, or mathematical expression. One example would be when you want to date and time stamp a row. Here you can use the getdate() function. Another example would be for auditing purposes. Perhaps you want to record the user that is adding the row. The below example adds the date and time into a column. 

alter table Tbl3
add constraint defDateTime default getdate() for DateTimeRowAdded

The default value comes into play only when new rows are added and when the user does not provide any data to fill that column.

Primary Key

The Primary Key is similar to the Unique constraint. NULLs are not allowed. With the Unique constraint, you can have at most one NULL. If you had two NULLs, then it would not be unique. The default primary key clusters.

Clustering is a fancy name for sorting. When you create a primary key that is clustered, that table is physically sorted. You can only have one primary key per table. This makes sense knowing that the table is physically sorted. You cannot sort the table on multiple columns at the same time.

The experts recommend that all your tables have a primary key, although they don’t have to. If you have a column in your table that looks like it uniquely identifies objects in the table, such as a Social Insurance Number or an Employee Number, use that one. If not, create a surrogate key. The general definition of surrogate is substitute or proxy or replacement. A surrogate key can be a number that has no resemblance to “real life”. The alternative to a surrogate key is a natural key.