Within any business, data must often adhere to certain restrictions or rules. For example, an employee number must be unique. DB2 provides constraints as a way to enforce such rules.
DB2 provides the following types of constraints:
Figure 11. Unique Constraints Prevent Duplicate Data
![]() |
The database manager enforces the constraint during insert and update operations, ensuring data integrity.
Because the primary key is used to identify a row in a table, it should be unique and have very few additions or deletions. A table cannot have more than one primary key, but it can have multiple unique keys. Primary keys are optional, and can be defined when a table is created or altered. They are also beneficial, because they order the data when data is exported or reorganized.
In the following tables, DEPTNO and EMPNO are the primary keys for the
DEPARTMENT and EMPLOYEE tables.
DEPTNO (Primary Key) | DEPTNAME | MGRNO |
---|---|---|
A00 | Spiffy Computer Service Division | 000010 |
B01 | Planning | 000020 |
C01 | Information Center | 000030 |
D11 | Manufacturing Systems | 000060 |
EMPNO (Primary Key) | FIRSTNAME | LASTNAME | WORKDEPT (Foreign Key) | PHONENO |
---|---|---|---|---|
000010 | Christine | Haas | A00 | 3978 |
000030 | Sally | Kwan | C01 | 4738 |
000060 | Irving | Stern | D11 | 6423 |
000120 | Sean | O'Connell | A00 | 2167 |
000140 | Heather | Nicholls | C01 | 1793 |
000170 | Masatoshi | Yoshimura | D11 | 2890 |
For example, a typical foreign key constraint might state that every employee in the EMPLOYEE table must be a member of an existing department, as defined in the DEPARTMENT table.
To establish this relationship, you would define the department number in the EMPLOYEE table as the foreign key, and the department number in the DEPARTMENT table as the primary key.
Figure 12. Foreign and Primary Key Constraints Define Relationships and Protect Data
![]() |
For example, in an EMPLOYEE table, you can define the Type of Job column to be "Sales", "Manager", or "Clerk". With this constraint, any record with a different value in the Type of Job column is not valid, and would be rejected, enforcing rules about the type of data allowed in the table.
You can also use triggers in your database. Triggers are more complex and potentially more powerful than constraints. They define a set of actions that are executed in conjunction with, or triggered by, an INSERT, UPDATE, or DELETE clause on a specified base table. You can use triggers to support general forms of integrity or business rules. For example, a trigger can check a customer's credit limit before an order is accepted, or be used in a banking application to raise an alert if a withdrawal from an account did not fit a customer's standard withdrawal patterns. For more information about triggers, refer to the Application Development Guide.