Effects of Constraints on DML Statements

Introduction

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

• Enforcing Data Integrity with Constraints
• Ignoring Constraints using BULK INSERT

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

Exam Objectives

Five exam objectives are covered in this lab.

• Demonstrate how to insert data into a table which has primary and foreign key constraint
• Demonstrate how a delete statement fails when the data being attempted to delete violates a foreign key constraint
• Demonstrate how to insert data into a table with CHECK constraint on a column
• Demonstrate how an update statement fails when the data being attempted to update violates a unique constraint
• Demonstrate how to bulk import data into a table with CHECK constraint on a column by specifying IGNORE CONSTRAINT

Exercise 1 - Enforcing Data Integrity with Constraints

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. The primary key column cannot contain NULL values.

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.

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 enforce data integrity with various constraints. You will view the effect of these constraints when used with various DML statements.

Exercise 2 - Ignoring Constraints using BULK INSERT

SQL Server allows you to import a data file into a database table.You can perform a bulk import using the BULK INSERT statement. The BULK INSERT statement ignores CHECK and FOREIGN KEY constraint, by default. However, the CHECK_CONSTRAINTS option in the BULK INSERT statement helps you to specify that all constraints on the target table must be checked during the bulk-import operation. Irrespective of the usage of CHECK_CONSTRAINTS option in the BULK INSERT statement, the UNIQUE and PRIMARY KEY constraints are always enforced. If the source text file does not provide a value for a character column that is defined with a NOT NULL constraint in the target table, the BULK INSERT statement inserts a blank space.

In this exercise, you will learn to work with the BULK INSERT statement to ignore constraints imposed on a target table.