SQL 2005+

SQL Server Unique Constraints (2)

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.

Constraint Properties

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.