Using Microsoft SQL Server Constraints

Introduction

In this article, I want to tell you about SQL Server constraints: what
a constraint is, when it is used and for which purposes. You can find
in this article some examples of constraint creation and SQL Server 2000
constraints enhancements.

General Concepts

A constraint is a property assigned to a column or the set of columns
in a table that prevents certain types of inconsistent data values from
being placed in the column(s). Constraints are used to enforce the data
integrity. This ensures the accuracy and reliability of the data in the
database. The following categories of the data integrity exist:

• Entity Integrity
• Domain Integrity
• Referential integrity
• User-Defined Integrity

Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type,
the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by
other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity enforces some specific business rules that do not fall
into entity, domain, or referential integrity categories.

Each of these categories of the data integrity can be enforced by the
appropriate constraints. Microsoft SQL Server supports the following
constraints:

• PRIMARY KEY
• UNIQUE
• FOREIGN KEY
• CHECK
• NOT NULL

A PRIMARY KEY constraint is a unique identifier for a row within a database
table. Every table should have a primary key constraint to uniquely identify
each row and only one primary key constraint can be created for each table.
The primary key constraints are used to enforce entity integrity.

A UNIQUE constraint enforces the uniqueness of the values in a set of
columns, so no duplicate values are entered. The unique key constraints
are used to enforce entity integrity as the primary key constraints.

A FOREIGN KEY constraint prevents any actions that would destroy links
between tables with the corresponding data values. A foreign key in one
table points to a primary key in another table. Foreign keys prevent
actions that would leave rows with foreign key values when there are
no primary keys with that value. The foreign key constraints are used
to enforce referential integrity.

A CHECK constraint is used to limit the values that can be placed in a
column. The check constraints are used to enforce domain integrity.

A NOT NULL constraint enforces that the column will not accept null
values. The not null constraints are used to enforce domain integrity,
as the check constraints.

You can create constraints when the table is created, as part of the
table definition by using the CREATE TABLE statement.

Examples

The following example creates a check_sale CHECK constraint on an employee
table:

CREATE TABLE employee(

   EmployeeId INT NOT NULL,

   LName VARCHAR(30) NOT NULL,

   FName VARCHAR(30) NOT NULL,

   Address VARCHAR(100) NOT NULL,

   HireDate DATETIME NOT NULL,

   Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0)

)

You can add constraints to an existing table by using the ALTER TABLE
statement. The following example adds a pk_employee primary key constraint
on an employee table:

ALTER TABLE employee

ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)

You can add the primary or unique key constraint into an existing
table only when there are no duplicate rows in the table. You can
drop constraints in an existing table by using the ALTER TABLE statement.
The following example drops the pk_employee primary key constraint in the
employee table:

ALTER TABLE employee

DROP CONSTRAINT pk_employee

Sometimes you need to perform some actions that require the FOREIGN KEY
or CHECK constraints be disabled, for example, your company does not hire
foreign employees, you made the appropriate constraint, but the situation
changed. Your boss needed to hire a foreign employee, but only
this one. In this case, you need to disable the constraint by using the
ALTER TABLE statement. After these actions are performed, you can
re-enable the FOREIGN KEY and CHECK constraints by using the ALTER TABLE
statement.

The following example disables the check_sale constraint in the employee
table and enables this constraint later:

— disable the check_sale constraint in the employee table

ALTER TABLE employee NOCHECK CONSTRAINT check_sale

— enable the check_sale constraint in the employee table

ALTER TABLE employee CHECK CONSTRAINT check_sale

SQL Server 2000 Constraints Enhancements

SQL Server 2000 introduces many new features. Some of them fall into the
constraints area. Now you can control the actions SQL Server 2000 takes
when you attempt to update or delete a key to which existing foreign keys
point. You can control it by using the new ON DELETE and ON UPDATE clauses
in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements.
For example, in the previous versions of SQL Server if you wanted to do a
cascade delete from the referenced table when the appropriate record in
the parent table is deleted, you had to create a trigger which executed
on delete of the parent table, but now you can simply specify the ON DELETE
clause in the REFERENCES clause.

The following example is used to create the Books and the Authors tables
and create a foreign key constraint which will perform the cascade delete
action, therefore, when a row in the Authors table is deleted, the
corresponding rows in the Books are also deleted:

CREATE TABLE Books (

   BookID INT NOT NULL PRIMARY KEY,

   AuthorID INT NOT NULL,

   BookName VARCHAR(100) NOT NULL,

   Price MONEY  NOT NULL

)

GO

CREATE TABLE Authors (

   AuthorID INT NOT NULL PRIMARY KEY,

   Name VARCHAR(100) NOT NULL

)

GO
ALTER TABLE Books

ADD CONSTRAINT fk_author

FOREIGN KEY (AuthorID)

REFERENCES Authors (AuthorID) ON DELETE CASCADE

GO

Conclusion

Constraints are the built-in mechanism for enforcing data integrity. Using
constraints is preferred to using triggers, rules, and defaults because
built-in integrity features use much less overhead and perform faster than
the ones you can create. When you write your own code to realize the same
actions as the constraints, there is a potential for error in the code, therefore,
the built-in constraints
are not only faster, but also are more consistent and reliable. So, you
should use triggers and rules only when the constraints do not provide all
the needed functionality.

»


See All Articles by Columnist Alexander Chigrik