9.3 Creating Constraints

A Primary Key is a candidate key to uniquely identify each row in a table. A table is allowed one Primary Key constraint, and a column that is part of the Primary Key constraint cannot take null values. Because Primary Key constraints ensure unique data, they are most often defined on an identity column. To create a Primary Key Constraint, enter a Primary Key Name in the name column of the Constraints table, then select Primary Key from the Type Column dropdown. In the Constraint Definition pane, select the table columns from the table that are part of the Primary key. A Primary Key Constraint is represented by a table icon with gold node when viewed in the Constraints container in the Schema Browser.

Foreign Key Constraints

A Foreign Key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. To create a Foreign Key Constraint, enter a Foreign Key Name in the name column of the Constraints table, then select Foreign Key from the Type Column dropdown. Select the Reference Schema and Reference Table. In the Constraint Definition pane, select the columns from the tables that are set in the Reference Table of the Foreign Key. A Foreign Key Constraint is represented by a table icon with gold node and black square when viewed in the Constraints container in the Schema Browser.

Unique Constraints

A Unique Constraint uniquely identifies all possible rows that exist in a table. Unique Constraints make sure that no identically copied values are entered in specific columns that do not participate in a primary key. Multiple Unique Constraints can exist on a single table. To create a Unique Constraint, enter a Unique Constraint name in the name column of the Constraints table, then select Unique Constraint from the Type Column dropdown. In the Constraint Definition pane, select the columns from the columns needed. A Unique Key Constraint is represented by a table icon with crossed node when viewed in the Constraints container in the Schema Browser.

Check Constraints

A Check Constraint (also known as table check constraint) is a condition that defines valid data when adding or updating an entry in a table of a relational database. Within the Constraints Tab of Create or Alter Table, the Check Condition column is where the condition is set, determining what the table considers valid data for the selected column. To create a Check Constraint, enter a Check Constraint Name in the name column of the Constraints table, then select Check Constraint from the Type Column dropdown. Enter the formula for the check in the Check Condition field. A Check Constraint is represented by a table icon with gold node and green check when viewed in the Constraints container in the Schema Browser.

Sample Table with Constraint Types

The screenshot labeled "Table with constraint types" shows the Alter Table dialog for a table from the AdventureWorks database in Microsoft SQL Server 2008.