 | Level: Introductory Roman Melnyk (roman_b_melnyk@hotmail.com), DB2 Information Development, IBM Canada Ltd.
01 Jan 2004 Constraints are used by DB2 UDB to enforce business rules for data and to help preserve database integrity. This article describes the various types of constraints supported by DB2 UDB 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).
Constraints are used by DB2(R) Universal Database(TM) (DB2 UDB) 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 can be used by the SQL compiler to improve query performance. In this article I will be focusing on the types of constraints listed above, and will not discuss Informational constraints.
You can define one or more DB2 UDB 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; so complex, in fact, that although only a small fraction of its options are used in constraints definitions, those options can themselves appear to be quite complex when viewed in a syntax diagram (Figures 1 and 2). Constraints management can be simpler and more convenient when done through the DB2 Control Center.
Figure 1. Partial syntax of the CREATE TABLE statement, showing clauses that are used in defining constraints
Constraints definitions are associated with the database to which they apply, and are stored in the database catalog (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 Control Center.
The constraints that you create are handled like 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)
Table 1. Constraints information in the database catalog. To run successfully, queries against the catalog require a database connection.
|
Catalog View
|
View Column
|
Description
|
Query Example
| | SYSCAT.CHECKS | | Contains a row for each table check constraint. | db2 select constname, tabname, text from syscat.checks | | SYSCAT.COLCHECKS | | Contains a row for each column that is referenced by a table check constraint. | db2 select constname, tabname, colname, usage from syscat.colchecks | | SYSCAT.COLUMNS | NULLS | Indicates whether a column is nullable (Y) or not nullable (N). | db2 select tabname, colname, nulls from syscat.columns where tabschema = 'MELNYK' and nulls = 'N' | | SYSCAT.CONSTDEP | | Contains a row for each dependency of a constraint on some other object. | db2 select constname, tabname, btype, bname from syscat.constdep | | SYSCAT.INDEXES | | Contains a row for each index. | db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'MELNYK' | | SYSCAT.KEYCOLUSE | | Contains a row for each column that participates in a key defined by a unique, primary key, or foreign key constraint. | db2 select constname, tabname, colname, colseq from syscat.keycoluse | | SYSCAT.REFERENCES | | Contains a row for each referential constraint. | db2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references | | SYSCAT.TABCONST | | Contains a row for each unique (U), primary key (P), foreign key (F), or table check (K) constraint. | db2 select constname, tabname, type from syscat.tabconst | | SYSCAT.TABLES | PARENTS | Number 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.TABLES | CHILDREN | Number 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.TABLES | SELFREFS | Number 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.TABLES | KEYUNIQUE | Number of unique constraints (other than primary key) defined on this table. | db2 "select tabname, keyunique from syscat.tables where keyunique > 0" | | SYSCAT.TABLES | CHECKCOUNT | Number of check constraints defined on this table. | db2 "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 every 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 will contain an employee 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 (Figures 3 and 4); or you can query the database catalog (Listing 1).
Figure 3. 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 list has been filtered on schema melnyk.
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 we select the EMPLOYEE table, we can open the Alter Table window to see the table definition, including column attributes (Figure 4).
Figure 4. The Alter Table window provides a convenient way to see a table's attributes.
Listing 1. Querying the database catalog to determine which database columns are nullable
db2 select tabname, colname, nulls
from syscat.columns
where tabschema = 'MELNYK' 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 (Figures 1 and 2), or in an ALTER TABLE statement as follows.
Listing 2. Creating 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 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')
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 "MELNYK.ORG_TEMP" from having duplicate rows for those columns.
SQLSTATE=23505
|
The unique constraint helps to ensure data integrity by preventing unintentional duplication. In our example, it 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.
|
Constraint names
If you do not specify a name for a constraint when it is created, DB2 will provide a name based on the creation timestamp. For example: SQL031229211328410.
|
|
"Numero uno!" - 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 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 (Figures 1 and 2), or in an ALTER TABLE statement as follows.
Listing 3. Creating a primary key constraint. The EMPNO column in the EMPLOYEE table is not nullable, and can have a primary key constraint defined on it.
db2 alter table employee add primary key (empno)
|
Alternatively, you can use the DB2 Control Center to define a primary key constraint on a table (Figure 5).
Figure 5. The Alter Table window provides a convenient way to define a primary key constraint on a table. Select one or more columns from the list of available columns and click the push button to move the selected column names to the primary key columns list. 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. Let's consider an 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 we know that EMPNO is now the primary key for the EMPLOYEE table, RESPEMP can be defined as a foreign key in the PROJECT table (Listing 4). This will ensure that future deletions from the EMPLOYEE table will not leave the PROJECT table with "non-existing" responsible employees.
A foreign key constraint can be defined in the CREATE TABLE statement using the FOREIGN KEY clause (Figures 1 and 2), or in an ALTER TABLE statement as follows.
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 (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 makes sense, and 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"; every dependent must have a parent.
The delete rule of a referential constraint applies when a row is deleted from a parent table, and depends on what option was specified when the referential constraint was defined. If the RESTRICT or the NO ACTION clause was specified, no rows are deleted. If the SET NULL clause was specified, each nullable column of the foreign key is set to null. However, if you specified the CASCADE option when you created the referential constraint, the delete operation is propagated to the dependents of the parent table. These dependents are said to be delete-connected to the parent table.
The following example illustrates 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 = '000190'
DB20000I The SQL command completed successfully.
db2 update employee set empno = '360' where empno = '000150'
SQL0531N The parent key in a parent row of relationship
"MELNYK.PROJECT.SQL040103212526610" cannot be updated. SQLSTATE=23504
db2 "select respemp from project where respemp < '000050' order by respemp"
RESPEMP
-------
000010
000010
000020
000030
000030
db2 delete from employee where empno = '000010'
DB20000I The SQL command completed successfully.
db2 "select respemp from project where respemp < '000050' order by respemp"
RESPEMP
-------
000020
000030
000030
|
The EMPNO value of '000190' in the parent table (EMPLOYEE) can be changed, because there is no RESPEMP value of '000190' in the dependent table (PROJECT). However, that is not the case for the EMPNO value of '000150', which has matching foreign key values in the PROJECT table, and which cannot therefore be updated. The delete rule specifying the CASCADE option ensures that when the primary key value of '000010' is deleted from the EMPLOYEE table, the delete-connected PROJECT table loses all of its rows that contain 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 (Figures 1 and 2), or in an ALTER TABLE statement as follows.
Listing 6. Creating a table check constraint. The PHONENO_LENGTH constraint ensures that telephone extensions added to the EMPLOYEE table are exactly four digits long.
db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)
|
Alternatively, you can use the DB2 Control Center to define a table check constraint (Figure 6).
Figure 6. The Alter Table window provides a convenient way to define a table check constraint on a column.
Click the Add push button to define a new constraint (the Add Check Constraint window opens), or the Change push button to modify an existing constraint that you have selected from the list (Figure 7).
Figure 7. The Change Check Constraint window lets you modify an existing check condition.
You cannot create a table check constraint if exisitng rows in the table contain values that violate the new constraint (Figure 8). You can successfully add or modify the constraint after the incompatible values are appropriately updated.
Figure 8. An error is returned if the new table check constraint is incompatible with existing values in the table.
|
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 very useful, for example, when optimizing performance during large data load operations against a table. Listing 7 presents 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.
|
Summary
We have explored the various types of constraints supported by DB2 Universal Database: the NOT NULL constraint, the unique constraint, the primary key constraint, the foreign key (referential) constraint, and table check constraints. We have shown how constraints are used by DB2 UDB to enforce business rules for data and to help preserve database integrity. We have also explained how to use both the command line and the DB2 Control Center (and how to query the database catalog) to effectively manage constraints.
Resources - The DB2 Technical Support site is the ideal place to locate resources such as the Version 8 Information Center and PDF product manuals.
About the author | | | Roman B. Melnyk , Ph.D., is a senior member of the DB2 Information Development team, specializing in database administration, DB2 utilities, and SQL. During more than nine years at IBM, Roman has written numerous DB2 books, articles, and other related materials. Roman coauthored DB2 Version 8: The Official Guide (Prentice Hall Professional Technical Reference, 2003), DB2: The Complete Reference (Osborne/McGraw-Hill, 2001), DB2 Fundamentals Certification for Dummies (Hungry Minds, 2001), and DB2 for Dummies (IDG Books, 2000). |
Rate this page
| |
