Examine the types of constraints supported by DB2 and explore examples of each type
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
- 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
Figure 2. Figure 1-A
Figure 3. Figure 1-B
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)
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 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 = ‘DELSVT’ 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 = ‘DELSVT’|
|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 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 5. Figure 3. View of tables in the Control Center
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 6. Figure 4. Alter Table screen in the Control Center
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
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
The Define Primary Key window appears, as shown in Figure 6.
Figure 6. The Define Primary Key window
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.
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 ACTION||No rows are deleted|
|SET NULL||Each nullable column of the foreign key is set to null|
|CASCADE||The 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
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
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
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.
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.