Constraints are used by IBM® DB2® for Linux®, UNIX®, and Windows® (DB2) to enforce business rules for data and to help preserve database integrity. This article describes the various types of constraints supported by DB2 and provides examples of each constraint type. In addition, the author explains the fundamentals of constraints management (using either the command line or the DB2 Control Center). [This article is updated to reflect DB2 9.7 and other updates.--Ed.]

Leon Kwok (leonkwok@ca.ibm.com), DB2 Client Support, IBM

Photo of Leon KwokLeon Kwok is a member of the IBM DB2 LUW Advanced Support team, and he specializes in the engine area.



13 May 2010 (First published 01 January 2004)

Also available in Russian Vietnamese Spanish

Introduction

This section describes the differences in the structures of the DB2 and solidDB SQL procedures.

Constraints are used by DB2 for Linux, UNIX, and Windows (DB2 LUW) to enforce business rules for data. This article describes the following types of constraints:

  • NOT NULL
  • Unique
  • Primary key
  • Foreign key
  • Table check

There is another type of constraint known as an informational constraint. Unlike the five constraint types listed above, an informational constraint is not enforced by the database manager, but it can be used by the SQL compiler to improve query performance. This article focuses on only the types of constraints in the list.

You can define one or more DB2 constraints when you create a new table, or you can define some of them by altering the table later. The CREATE TABLE statement is very complex. In fact, it is so complex that although only a small fraction of its options are used in constraint definitions, those options can themselves appear to be quite complex when viewed in a syntax diagram, as shown in Figure 1.

Figure 1. Partial syntax of the CREATE TABLE statement, showing clauses that are used in defining constraints
CREATE TABLE table-name |element list|. Element list includes column definition, unique-constraint, referential constraint, and check-constraint
Figure 1-A
Column-definition and column-options. One of column options is CONSTRAINT constraint-name
Figure 1-B
References-clause and rule-clause. Constraint-attributes include ON DELETE NO ACTION, ON DELETE CASCADE, ENFORCED, AND DISABLE QUERY OPTIMIZATION

Constraints management can be simpler and more convenient when done through the DB2 Control Center.

Constraints definitions are associated with the database to which they apply, and they are stored in the database catalog, as shown in Table 1. You can query the database catalog to retrieve and inspect this information. You can do so directly from the command line (remember to establish a database connection first), or, again, you might find it more convenient to access some of this information through the DB2 Control Center.

The constraints that you create are handled like any other database objects. They are named, have an associated schema (creator ID), and in some cases can be dropped (deleted).

Figure 2. Partial syntax of the CREATE TABLE statement, showing clauses that are used in defining constraints (continued)
Unique, Referential, and Check-constraints, plus Check-condition and Functional-dependency

Table 1 shows constraints information in the database catalog. To run successfully, queries against the catalog require a database connection.

Table 1. Constraints information in the database catalog
Catalog viewView columnDescriptionQuery example
SYSCAT.CHECKSContains a row for each table check constraintdb2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKSContains a row for each column that is referenced by a table check constraintdb2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNSNULLSIndicates whether a column is nullable (Y) or not nullable (N)db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'
SYSCAT.CONSTDEPContains a row for each dependency of a constraint on some other objectdb2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXESContains a row for each index.db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'
SYSCAT.KEYCOLUSEContains a row for each column that participates in a key defined by a unique, primary key, or foreign key constraintdb2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCESContains a row for each referential constraintdb2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONSTContains a row for each unique (U), primary key (P), foreign key (F), or table check (K) constraintdb2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLESPARENTSNumber of parent tables of this table (the number of referential constraints in which this table is a dependent)db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLESCHILDRENNumber of dependent tables of this table (the number of referential constraints in which this table is a parent)db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLESSELFREFSNumber of self-referencing referential constraints for this table (the number of referential constraints in which this table is both a parent and a dependent)db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLESKEYUNIQUENumber of unique constraints (other than primary key) defined on this tabledb2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLESCHECKCOUNTNumber of check constraints defined on this tabledb2 "select tabname, checkcount from syscat.tables where checkcount > 0"

"Can't be nuthin'!" - The NOT NULL constraint

The NOT NULL constraint prevents null values from being added to a column. This ensures that the column has a meaningful value for each row in the table. For example, the definition of the EMPLOYEE table in the SAMPLE database includes LASTNAME VARCHAR(15) NOT NULL, which ensures that each row contains an employee's last name.

To determine whether a column is nullable, you can refer to the data definition language (DDL) for the table (which you can generate by invoking the db2look utility). You can use the DB2 Control Center, as shown in Figure 3 and Figure 4.

Figure 3. View of tables in the Control Center
The tables that are associated with a particular database appear in the contents pane of the DB2 Control Center with Tables selected in the object tree. The drop-down highlights Alter.

The DB2 Control Center lets you conveniently access database objects such as tables. Figure 3 shows the user tables in the SAMPLE database. They appear in the contents pane when Tables is selected in the object tree. If you select the STAFF table, you can open the Alter Table window to see the table definition, including the column attributes shown in Figure 4.

Figure 4. Alter Table screen in the Control Center
Alter Table - STAFF window showing attributes such as ID, Name, Dept, and so on

Or you can query the database catalog, as shown in Listing 1.

Listing 1. Querying the database catalog to determine which table columns are nullable
db2 select tabname, colname, nulls
    from syscat.columns
    where tabschema = 'DELSVT' and nulls = 'N'

"For singles only" - The unique constraint

The unique constraint prevents a value from appearing more than once within a particular column in a table. It also prevents a set of values from appearing more than once within a particular set of columns. Columns that are referenced in a unique constraint must be defined as NOT NULL. The unique constraint can be defined in the CREATE TABLE statement using the UNIQUE clause (Figure 1 and Figure 2), or in an ALTER TABLE statement, as shown in Listing 2.

Listing 2 shows how to create a unique constraint. The ORG_TEMP table is identical to the ORG table in the SAMPLE database, except that the LOCATION column in ORG_TEMP is not nullable, and the LOCATION column can have a unique constraint defined on it.

Listing 2. Creating a unique constraint
db2 create table org_temp (
    deptnumb smallint not null,
    deptname varchar(14),
    manager smallint,
    division varchar(10),
    location varchar(13) not null)

db2 alter table org_temp add unique (location)

db2 insert into org_temp
    values (10, 'Head Office', 160, 'Corporate', 'New York')

DB20000I  The SQL command completed successfully.

db2 insert into org_temp
    values (15, 'New England', 50, 'Eastern', 'New York')

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "DELSVT.ORG_TEMP" from having duplicate values for the index key.
SQLSTATE=23505

Constraint names

If you do not specify a name for a constraint when it is created, DB2 assigns a name based on the creation timestamp, such as SQL100419222516560.

The unique constraint helps to ensure data integrity by preventing unintentional duplication. In the example, the unique constraint prevents the insertion of a second record specifying New York as a branch location for the organization. The unique constraint is enforced through a unique index.


"We're number one!" - The primary key constraint

The primary key constraint ensures that all values in the column or the set of columns that make up the primary key for a table are unique. The primary key is used to identify specific rows in the table. A table cannot have more than one primary key, but it can have several unique keys. The primary key constraint is a special case of the unique constraint, and it is enforced through a primary index.

Columns that are referenced in a primary key constraint must be defined as NOT NULL. The primary key constraint can be defined in the CREATE TABLE statement using the PRIMARY KEY clause (see Figure 1 and Figure 2), or in an ALTER TABLE statement as shown in Listing 3.

Listing 3 shows how to create a primary key constraint. The ID column in the STAFF table is not nullable, and it can have a primary key constraint defined on it.

Listing 3. Creating a primary key constraint
db2 alter table staff add primary key (id)

Alternatively, you can use the DB2 Control Center to define a primary key constraint on a table, as shown in Figure 5 and Figure 6. The Alter Table window provides a convenient way to define a primary key constraint on a table. Select the Keys tab, then click Add Primary.

Figure 5. The Alter Table window
Alter Table - STAFF window showing Keys tab and Add Primary for selection

The Define Primary Key window appears, as shown in Figure 6.

Figure 6. The Define Primary Key window
Define Primary Key window with CC1271998584532 as Primary Key Name and ID as Selected Columns

The Define Primary Key window enables you select one or more columns from the Available column list. Click the > button to move names from the Available column list to the Selected column. Note that the selected columns must not be nullable.


"It's all relative" - The foreign key constraint

The foreign key constraint is sometimes referred to as the referential constraint. Referential integrity is defined as the state of a database in which all values of all foreign keys are valid. So what is a foreign key? A foreign key is a column or a set of columns in a table whose values must match at least one primary key or unique key value of a row in its parent table. What exactly does that mean? It's actually not as bad as it sounds. It simply means that if a column (C2) in a table (T2) has values that match values in a column (C1) of another table (T1), and C1 is the primary key column for T1, then C2 is a foreign key column in T2. The table containing the parent key (a primary key or a unique key) is called the parent table, and the table containing the foreign key is called the dependent table. Consider the following example.

The PROJECT table in the SAMPLE database has a column called RESPEMP. Values in this column represent the employee numbers of the employees who are responsible for each project listed in the table. RESPEMP is not nullable. Because this column corresponds to the EMPNO column in the EMPLOYEE table, and EMPNO is now the primary key for the EMPLOYEE table, RESPEMP can be defined as a foreign key in the PROJECT table, as shown in Listing 4. This ensures that future deletions from the EMPLOYEE table will not leave the PROJECT table with non-existent responsible employees.

A foreign key constraint can be defined in the CREATE TABLE statement using the FOREIGN KEY clause (see Figure 1 and Figure 2), or in an ALTER TABLE statement, as shown in Listing 4.

Listing 4. Creating a foreign key constraint
db2 alter table project add foreign key (respemp) references employee on delete cascade

The REFERENCES clause points to the parent table for this referential constraint. The syntax for defining a foreign key constraint includes a rule-clause, which is where you can tell DB2 how you want update or delete operations handled from a referential integrity perspective (see Figure 1).

Insert operations are handled in a standard way over which you have no control. The insert rule of a referential constraint is that an insert value of the foreign key must match some value of the parent key of the parent table. This is consistent with what has already been said. If a new record is to be inserted into the PROJECT table, that record must contain a reference (through the parent-foreign key relationship) to an existing record in the EMPLOYEE table.

The update rule of a referential constraint is that an update value of the foreign key must match some value of the parent key of the parent table, and that all foreign key values must have matching parent key values when an update operation on the parent key completes. Again, all that this means is that there cannot be any orphans, and each dependent must have a parent.

The delete rule of a referential constraint applies when a row is deleted from a parent table, depending on what option was specified when the referential constraint was defined.

Table 2. Referential constraint options
If this clause was specified when the referential restraint was created...Then this is the result
RESTRICT or NO ACTIONNo rows are deleted
SET NULLEach nullable column of the foreign key is set to null
CASCADEThe delete operation is propagated to the dependents of the parent table. These dependents are said to be delete-connected to the parent table.

Listing 5 shows some of these points.

Listing 5. Demonstrating the update rule and the delete rule in a foreign key constraint
db2 update employee set empno = '350' where empno = '000200'
DB20000I  The SQL command completed successfully.

db2 update employee set empno = '360' where empno = '000220'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0531N  The parent key in a parent row of relationship
"DELSVT.PROJECT.FK_PROJECT_2" cannot be updated.  SQLSTATE=23504

db2 "select respemp from project where respemp < '000050' order by respemp"

RESPEMP
-------
000010
000010
000020
000030
000030

  5 record(s) selected.

db2 delete from employee where empno = '000010'
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0532N  A parent row cannot be deleted because the relationship
"DELSVT.PROJECT.FK_PROJECT_2" restricts the deletion.  SQLSTATE=23001

db2 "select empno from employee where empno < '000050' order by empno"

EMPNO
------
000010
000020
000030

  3 record(s) selected.

The EMPNO value of 000200 in the parent table (EMPLOYEE) can be changed, because there is no RESPEMP value of 000200 in the dependent table (PROJECT). However, for the EMPNO value of 000220, it has matching foreign key values in the PROJECT table, and therefore, it cannot be updated. The delete rule specifying the RESTRICT option ensures that no rows that contain the primary key value of 000010 can be deleted from the EMPLOYEE table when the delete-connected PROJECT table contains the matching foreign key value.


"Check and check again" - The table check constraint

A table check constraint enforces defined restrictions on data being added to a table. For example, a table check constraint can ensure that the telephone extension for an employee is exactly four digits long whenever telephone extensions are added or updated in the EMPLOYEE table. Table check constraints can be defined in the CREATE TABLE statement using the CHECK clause (see Figure 1 and Figure 2), or in an ALTER TABLE statement, as shown in Listing 6.

Listing 6. Creating a table check constraint
db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

The PHONENO_LENGTH constraint ensures that telephone extensions added to the EMPLOYEE table are exactly four digits long.

Alternatively, you can use the DB2 Control Center to define a table check constraint, as shown in Figure 7.

Figure 7. The Alter Table window provides a convenient way to define a table check constraint on a column
Alter Table - EMPLOYEE window with Check Constraints tab selected showing PHONENO_LENGTH with condition LENGTH(RTRIM(PHONENO))=4

Click the Add button to define a new constraint, and the Add Check Constraint window opens. Or click the Change button to modify an existing constraint that you have selected from the list, as shown in Figure 8.

Figure 8. The Change Check Constraint window lets you modify an existing check condition
Change Check Constraint with LENGTH(RTRIM(PHONENO))=4 as Check Condition for PHONENO_LENGTH

You cannot create a table check constraint if existing rows in the table contain values that violate the new constraint, as shown in Figure 9. You can successfully add or modify the constraint after the incompatible values are appropriately updated.

Figure 9. An error is returned if the new table check constraint is incompatible with existing values in the table
Error message SQL00544 says the check constraint cannot be added because the table contains a row that violates the constraint

Deferring data checks

The SET INTEGRITY statement can be used to put a table in check-pending state. This allows execution of the ALTER TABLE statement defining a new check constraint to proceed without any checking of existing data in the table.

Table check constraints can be turned on or off using the SET INTEGRITY statement. This can be useful, for example, when optimizing performance during large data load operations against a table. Listing 7 shows how to code a simple scenario showing one possible approach to using the SET INTEGRITY statement. In this example, the telephone extension for employee 000100 is updated to a value of 123, after which integrity checking of the EMPLOYEE table is turned off. A check constraint requiring 4-digit telephone extension values is defined on the EMPLOYEE table. An exception table called EMPL_EXCEPT is created. The definition of this new table mirrors that of the EMPLOYEE table. Integrity checking is turned on, with rows in violation of the check constraint being written to the exception table. Queries against these tables confirm that the row in question now exists only in the exception table.

Listing 7. Using the SET INTEGRITY statement to defer constraints checking
db2 update employee set phoneno = '123' where empno = '000100'

db2 set integrity for employee off

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

db2 create table empl_except like employee

db2 set integrity for employee immediate checked for exception in employee use empl_except
SQL3602W  Check data processing found constraint violations and moved them to
exception tables.  SQLSTATE=01603

db2 select empno, lastname, workdept, phoneno from empl_except

EMPNO  LASTNAME        WORKDEPT PHONENO
------ --------------- -------- -------
000100 SPENSER         E21      123

  1 record(s) selected.

Conclusion

This article explored the various types of constraints supported by DB2 for Linux, UNIX, and Windows, including the NOT NULL constraint, the unique constraint, the primary key constraint, the foreign key (referential) constraint, and table check constraints. DB2 uses constraints to enforce business rules for data and to help preserve database integrity. You also learned how to use both the command line and the DB2 Control Center (and how to query the database catalog) to effectively manage constraints.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14105
ArticleTitle=DB2 Basics: Constraints
publish-date=05132010