Creating Constraints

Introduction

The Creating Constraints module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with primary keys
• Working with foreign keys
• Working with constraints

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Six exam objectives are covered in this lab.

• Demonstrate how to define a column as primary key for an existing table using SQL Server Management Studio
• Demonstrate how to define a column as primary key for an existing table using Transact-SQL
• Demonstrate how to create a foreign key constraint in an existing table which refers to another column in the same table using Transact-SQL
• Demonstrate how to create a foreign key constraint in an existing table which refers to another table in the same database using Transact-SQL
• Demonstrate how to create a check constraint on one of the columns in an existing table
• Demonstrate how to create a unique constraint on one of the columns of an existing table

Exercise 1 - Working with Primary Keys

The primary key is one of the types of constraints that you can use to apply data integrity in databases. A primary key column uniquely identifies the record in a database table. In other words, you cannot enter duplicate values in the primary key column. The database automatically creates a unique index for the primary key column. There can be only one primary key constraint in a single table. The primary key column cannot contain NULL values.

In this exercise, you will learn to work with primary keys using SQL Server Management Studio and Transact-SQL statements.

Exercise 2 - Working with Foreign Keys

Foreign keys are another type of constraint that can be created in database tables. You can create foreign key constraints to establish relationships between or within tables in the database. If you set the foreign key constraint in the table, then it is recommended to link that column to a primary key in another or same table. The foreign key columns accept only those values that exist in the primary key columns. However, it can contain NULL values.

In this exercise, you will learn to work with foreign keys in tables.

Exercise 3 - Working with Constraints

Apart from primary and foreign key constraints, there are two more types of constraints namely CHECK and UNIQUE that can enforce data integrity in SQL Server databases.

You can create a CHECK constraint to restrict the entry of values in table column(s). The restriction of values in table columns can be specified in a CHECK constraint using a Boolean expression along with the logical operators.

You can create a UNIQUE constraint on a column to ensure the non-entry of duplicate values. This constraint can be created on any column apart from the primary key column. The columns with UNIQUE constraint can accept NULL values.

In this exercise, you will learn to work with CHECK and UNIQUE constraints.