How to Constrain Values in SQL Server 2005 - dummies

How to Constrain Values in SQL Server 2005

By Andrew Watt

Part of Microsoft SQL Server 2005 For Dummies Cheat Sheet

Constraints (rules enforced by SQL Server 2005) offer a key way to ensure data integrity. SQL Server 2005 enables you to constrain the values allowed in a particular column so that inappropriate data is not permitted.

SQL Server 2005 supports five types of constraint:

  • Not null: When this constraint is specified, you are not allowed to have a field in a column with no data in it.

  • Check: This constraint specifies allowed values. For example, you might want age of employees to be between 16 and 65.

  • Unique: This constraint specifies that you can’t repeat the value in a field in any other field in the same column.

  • Primary key: This constraint specifies a unique identifier for the value in a field, which uniquely identifies a row.

  • Foreign key: This constraint references a unique identifier in another table in the database.

(The datatype in the Table Designer also constrains the values allowed in a column but is not considered to be a constraint.)

When you create a table, you typically add a primary key to the table. A table may have only one primary key.

If you do not designate a column (or combination of columns) as the primary key when you create the table, you can specify a primary key later, provided certain conditions are met. A column intended as a primary key can’t have NULL values and each value in the column must be unique. If the table has data and those conditions are not met, your attempt to create a primary key on that column causes an error and no primary key is created.

Choose a primary key when you first create a table. By doing so, you avoid possible time-consuming changes to a table after it contains data.

If you want to change the column (or combination of columns) that is the primary key, you need to delete the primary key constraint on the original column(s) and add a primary key constraint. If the original primary key is referenced by a foreign key constraint, you must delete the foreign key constraint first, and then delete the original primary key constraint.

To add a check constraint, follow these steps:

  1. Right-click the row that specifies the relevant column.

  2. Choose Check Constraints from the context menu.

  3. In the Check Constraints dialog box, click the Add button to add a new constraint.

  4. Click the ellipsis in the Expression row of the right pane of the Check Constraints window.

    The Check Constraints dialog box opens.

  5. Enter an expression that specifies the constraint.

    The allowed expressions depend on the datatype of the column. For example, in an Age column, you can specify that allowed ages are between 16 and 70 by using the following expression:

Age >= 16 AND Age <= 70