Road Map to DB2 Programming

Constraints

	SQL Reference	Describes SQL syntax, semantics, and the rules of the language. Chapter 2 contains details about constraints.
	Administration Guide	Contains information required to design, implement, and maintain a database. Chapter 4 contains details about constraints.

		Embedded SQL	DB2 CLI
	trigsql	X		Demonstrates triggers and constraints.

To protect data and to define relationships between your data, you usually define business rules (or rules). Rules define what data values are valid for a column in a table, or how columns in one or more tables are related to each other.

DB2 provides constraints as a way to enforce those rules using the database system. By using the database system to enforce business rules, you don't have to write code in your application to enforce them. However, if a business rule applies to one application only, you should code it in the application instead of using a global database constraint.

DB2 provides different kinds of constraints. You define constraints using the SQL statements CREATE TABLE and ALTER TABLE.

NOT NULL constraints

NOT NULL constraints prevent null values from being entered into a column.

UNIQUE constraints

UNIQUE constraints ensure that the values in a set of columns are unique and not null for all rows in the table. For example, a typical UNIQUE constraint in a DEPARTMENT table might be that the department number is unique and not null.

Figure 9. UNIQUE constraints prevent duplicate data

The database manager enforces the constraint during insert and update operations, ensuring data integrity.

PRIMARY KEY constraint

Each table can have one PRIMARY KEY. A PRIMARY KEY is a column or combination of columns that has the same properties as a UNIQUE constraint.

FOREIGN KEY constraints

FOREIGN KEY constraints (also known as referential integrity constraints) enable you to define required relationships between and within tables.

A FOREIGN KEY references a set of columns in either the same table or another table that comprise a PRIMARY KEY or a UNIQUE constraint. This creates a constraint so that any value in the FOREIGN KEY must match an existing value in the referenced key (either the PRIMARY KEY or a UNIQUE constraint).

For example, you might want to ensure that every employee in the EMPLOYEE table is a member of an existing department as defined in the DEPARTMENT table. To establish this relationship, you 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 10. FOREIGN and PRIMARY KEY constraints define relationships and protect data

With this relationship, you can control updates to the EMPLOYEE table by ensuring that the department number is valid, as defined in the DEPARTMENT table. The database can reject any EMPLOYEE record with a department number that is not valid. This ensures data integrity.

CHECK constraints

A CHECK constraint is a rule in the database that specifies the values allowed in one or more columns of every row of a table.

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 invalid and would be rejected, enforcing rules about the type of data allowed in the table

[ DB2 List of Books | Search the DB2 Books ]