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.

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).

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

Back to top

"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.

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.

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

db2 select tabname, colname, nulls
    from syscat.columns
    where tabschema = 'DELSVT' and nulls = 'N'

Back to top

"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.

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

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.

Back to top

"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.

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.

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

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.

Back to top

"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.

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.

Listing 5 shows some of these points.

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.

Back to top

"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.

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.

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.

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.

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.

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.

Back to top

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

• See the entire DB2 Basics series.
  
  
• Refer to the IBM Support Portal site for an ideal place to locate resources such as the DB2 Information Center (Versions 9.1, 9.5, and 9.7) and PDF product manuals.
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download a free trial version of DB2 9.7 for Linux, UNIX, and Windows.
  
  
• Download DB2 Express-C 9.7, a no-charge version of DB2 Express database server for the community.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

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

Rate this article

Comments

Back to top

Table of contents

• Introduction
• "Can't be nuthin'!" - The NOT NULL constraint
• "For singles only" - The unique constraint
• "We're number one!" - The primary key constraint
• "It's all relative" - The foreign key constraint
• "Check and check again" - The table check constraint
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers