31 Oct Sql server tutorial 04 Enforcing Data Integrity
- Key Skills & Concepts
- Understand Integrity Constraints
- Use NOT NULL Constraints
- Add UNIQUE Constraints
- Add PRIMARY KEY Constraints
- Add FOREIGN KEY Constraints
- Define CHECK Constraints
In SQL database must do more than just store data. It must ensure that the data it
stores is correct. If the integrity of the data is compromised, the data might be
inaccurate or inconsistent, bringing into question the reliability of the database itself. In
order to ensure the integrity of the data, SQL provides a number of integrity constraints,
rules that are applied to base tables to constrain the values that can be placed into those
tables. You can apply constraints to individual columns, to individual tables, or to multiple
tables. In this chapter, I discuss each type of constraint and explain how you can apply
them to your SQL database.
Understand Integrity Constraints
SQL integrity constraints, which are usually referred to simply as constraints, can be
divided into three categories:
- Table-related constraints A type of constraint that is defined within a table
definition. The constraint can be defined as part of the column definition or as an
element in the table definition. Constraints defined at the table level can apply to one
or more columns.
- Assertions A type of constraint that is defined within an assertion definition
(separate from the table definition). An assertion can be related to one or more tables.
- Domain constraints A type of constraint that is defined within a domain definition
(separate from the table definition). A domain constraint is associated with any column
that is defined within the specific domain.
Of these three categories of constraints, table-related constraints are the most common
and include the greatest number of constraint options. Table-related constraints can be
divided into two subcategories: table constraints and column constraints. The constraints
in both these subcategories are defined in the table definition. A column constraint is
included with the column definition, and a table constraint is included as a table element,
similar to the way columns are defined as table elements. (Chapter 3 discusses table
elements and column definitions.) Both column constraints and table constraints support a
number of different types of constraints. This is not the case for assertions and domain
constraints, which are limited to only one type of constraint. Figure 4-1 provides an
overview of the types of constraints that can be created.
Figure 4-1 Types of SQL integrity constraints
At the top of the illustration, you can see the three categories of constraints. Beneath
the Table-Related Constraints category are the Column Constraints subcategory and the
Table Constraints subcategory, each of which contains specific types of constraints. For
example, table constraints can include unique (UNIQUE constraints and PRIMARY KEY
constraints), referential (FOREIGN KEY constraints), and CHECK constraints, while
column constraints can include the NOT NULL constraint as well as unique, referential,
and CHECK constraints. However, domains and assertions support only CHECK
In some places, the SQL standard uses the term “table constraint” to refer to both types of table-related
constraints. I use the term “table-related” to avoid confusion.
As Figure 4-1 shows, there are five different types of constraints: NOT NULL,
UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. In SQL, UNIQUE constraints
and PRIMARY KEY constraints are both considered unique constraints, and FOREIGN
KEY constraints are considered referential constraints. The rest of the chapter is devoted
to explaining what each of these constraints means and how to apply them.
Use NOT NULL Constraints
In Chapter 3, I told you that null signifies that a value is undefined or not known. This is
not the same as a zero, a blank, an empty string, or a default value. Instead, it indicates
that a data value is absent. You can think of a null value as being a flag. (A flag is a
character, number, or bit that indicates a certain fact about a column. The flag serves as a
marker that designates a particular condition or existence of something.) In the case of
null, if no value is provided for a column, the flag is set, indicating that the value is
unknown, or null. Every column has a nullability characteristic that indicates whether the
column will accept null values. By default, all columns accept null values. However, you
can override the default nullability characteristic by using a NOT NULL constraint, which
indicates that the column will not accept null values.
Some RDBMSs allow you to change the default nullability of any new column you create. In addition, most
systems support a NULL constraint, which you can use to designate that a column will accept null values.
The NOT NULL constraint can only be used as a column constraint. It is not supported
for table constraints, assertions, or domain constraints. Implementing a NOT NULL
constraint is a very straightforward process. Simply use the following syntax when
creating a column definition.