SQL Server Unique Constraints
The fourteenth part of the SQL Server Programming Fundamentals tutorial discusses unique constraints. This type of constraint adds a rule to a table to ensure that no two rows contain duplicate information within the constrained columns.
What is a Unique Constraint?
A unique constraint provides similar functionality to a primary key. For this reason, they are often referred to as alternate keys. Such a key references one or more columns in a table and ensures that the data in those columns is unique. In the case of constraint with multiple columns defined, individual columns may include duplicated values but the combination of all of the specified columns must be unique.
Unlike with primary keys, unique constraints can include columns that are nullable. Any null values in the columns are considered when determining the uniqueness for a row so, for example, it is not possible to have two null values in a column defined in a single-column unique constraint. You may have several null values in multi-column constraints if the combination of data from all columns is unique within the table.
Another key difference between primary keys and unique constraints is that you may define more than one unique constraint on a single table. As foreign key relationships can be linked to a primary key or alternate key, this permits several relationships to be created using different unique keys within the same table.
Each constraint automatically includes an index to support the uniqueness of the columns referenced. The index improves the performance of the uniqueness tests.
Adding a Unique Constraint to a Table
In this article we will consider two methods of adding a unique constraint to a table. These will be using the SQL Server Management Studio (SSMS) and Transact-SQL (T-SQL) commands.
We will add the unique constraints to tables in the JoBS tutorial database. This has been created over the course of the tutorial so far. To prepare the tutorial database, download and run the creation script using the link at the start of this page. This will create the database and some sample data.
Adding a Unique Constraint Using SQL Server Management Studio
Unique constraints can be created, modified and deleted using the table designer in SSMS. We will begin by adding a unique constraint to the GeographicalAreas table. This constraint will reference a single column to ensure that the name of each geographical cannot be duplicated. The sample data in this table is already unique though nothing exists to enforce this. This is important as if you try to add a unique constraint to columns that contain duplicated data, the creation of the new rule will fail.
To begin, right-click the GeographicalAreas table in the Object Explorer in SSMS. From the context-sensitive menu that appears, select "Design". The table designer window and associated Table Designer menu will be displayed. To begin the process of adding an alternate key, select "Indexes/Keys" from the new menu, or click the appropriate toolbar button. This shows the Indexes/Keys dialog box, pictured below:
When the dialog box is first displayed, you should see that one key is already defined in the list box at the left of the window. This is the primary key for the table. It is possible to modify this key using the dialog box.
To add a new key, click the Add button. A new index with a default name will be added to the list and selected automatically. The key name will be marked with an asterisk (*) to indicate that it has yet to be saved into the database schema.
With the correct item selected in the list box, the details of the index or key can be modified using the property list at the right side of the dialog box. Many of these options are used for indexes only but some are applicable to unique constraints. These are:
- Type. The first property that should be modified is the Type option in the General section of the property list. This allows you to specify that the new item is either an index or a unique key. Click on the property to show a drop-down list containing the two possible values, then select "Unique Key". Many of the properties in the list will then be disabled, as these are only appropriate for index definitions.
- Columns. The Columns property defines the list of columns that are included in the key, the combination of which must be unique for each row in the table. When selected, a lookup button appears. Click this button and select the AreaName column in the dialog box that appears. The dialog box should appear as shown below. Click OK to accept the changes.
- Name. The Name property allows you to create a unique name for the constraint. Replace the default name with "UQ_GeographicalAreaName".
- Create as Clustered. This option allows you to specify whether the index that supports the unique constraint is clustered. A clustered index changes the physical order of the data within the table to provide the best possible performance for the index. This option will be disabled as the primary key for the GeographicalAreas table is already clustered and only one such index is permitted per table.
- Data Space Specification / Fill Specification. These options allow further control over the supporting index. They will be described in a later article regarding indexes. The default values will suffice in this case.
To store the new constraint, click the Close button, then save the table schema and close the table designer. You can test the constraint by right-clicking the table name and choosing Open Table. Try to add a new row with an area code of 'AVN', which is not already in use, and an area name of "Avon". As "Avon" already exists in the AreaName column, the new row cannot be saved.
13 October 2008