Data Integrity with DB2 for z/OS

Chapter 2. Semantic integrity 37

Table 2-2 String data types - Contain alphanumeric characters

The date and time data types are listed in Table 2-3.

Table 2-3 Date and time data types - Contain dates and times

From a user perspective, the date and time values appear to be character strings; however,

they are physically stored as binary packed strings.

As an example, the following three columns defined in the EMPLOYEE table have the three

data types mentioned:

 EMPNO CHAR(6) NOT NULL

Employee identification number. It can use alphanumeric characters.

 HIREDATE DATE

Date of hire. Using this built-in data, you will avoid non-date values in this field.

 SALARY DECIMAL(9,2)

Annual salary in dollars.

2.1.2 NOT NULL constraints

We use the NULL values to represent unknown or missing data. But sometimes the business

rules declare a null value unacceptable. For instance, in the EMPLOYEE table, each

EMPLOYEE must have an identification number. In this case, you can use the NOT NULL

constraint to ensure that this specific column will always have data on it.

There are situations where you do not want NULL values, you have a default value defined by

your business rules, in cases such as hire date. You can always put a system date if hire date

was not provided.

2.1.3 Unique constraint

By default, a unique constraint is an SQL rule that ensures that no two values in the same

column or in a specific group of columns are the same (entity integrity). For instance, in an

Data type Description

CHAR For fixed-length character string. The length is between 1 to 254 characters.

VARCHAR For varying-length character string. The length is up to 32,672 characters.

GRAPHIC For double-byte character set (DBCS) strings up to 127 bytes in length.

Data type Description

DATE Dates with three-part value that represent year, month, and day.

TIME Times with three-part value that represent hour, minute, and second.

TIMESTAMP Timestamps with seven-part value that represents date and time by year, month,

day, hour, minute, second, and microsecond.

Note: NULL value is not the same as blank strings or zeros. NULL values are considered,

but they are not included in the results of aggregate functions, such as MIN, MAX, AVG,

and SUM.

38 Data Integrity with DB2 for z/OS

employee table, the ID column must be unique because it represents one and only one

employee. Enforcing the unique constraint in the EMPLOYEE table helps accesses and

avoids insertion of a new employee using the same ID.

A unique constraint can be established using the clauses PRIMARY KEY (see 3.5.1, “Primary

key” on page 82) or UNIQUE in the CREATE TABLE or ALTER TABLE. The columns specified

in a unique constraint must be defined as NOT NULL or NOT NULL WITH DEFAULT and the

data type cannot be LOB or ROWID (including a distinct type that is based on a ROWID data

type).

A unique index enforces the uniqueness of the key during changes to the columns of the

unique constraint. For each unique constraint, an unique index must be created. If the unique

index already exists for the same set of columns, it will assume this index as the unique

constraint rule to enforce integrity.

CREATE TABLE with UNIQUE constraint

There are two places where you can define the unique clause in the CREATE statement:

 In the column definition, as shown in Example 2-1.

Example 2-1 Unique clause besides column definition

CREATE TABLE SAMPLE.EMP

(EMPNO CHAR(6) NOT NULL UNIQUE,

NAME VARCHAR(60) NOT NULL,

WORKDEPT CHAR(3),

) IN DATAINT.EMPTS

--

CREATE TABLE SAMPLE.EMP

(EMPNO CHAR(6) NOT NULL CONSTRAINT EMPNO UNIQUE,

NAME VARCHAR(60) NOT NULL,

WORKDEPT CHAR(3),

) IN DATAINT.EMPTS

 At the end of column definition, as shown in Example 2-2. With this option, you can refer to

more than one column in the same unique constraint clause.

Example 2-2 Unique clause at the end of column definition

CREATE TABLE SAMPLE.EMP

(EMPNO CHAR(6) NOT NULL UNIQUE,

NAME VARCHAR(60) NOT NULL,

WORKDEPT CHAR(3),

CONSTRAINT EMPNO_NAME UNIQUE (EMPNO, NAME)

) IN DATAINT.EMPTS

The table definition is incomplete at this point. As unique constraint is enforced by a unique

index, you must create a unique index with the same columns of the unique constraint.

Note: DB2 implicitly creates an index to enforce the uniqueness of the primary key and the

table definition is considered complete only if the CREATE statement is processed by the

schema processor. For information about the schema processor (DSNHSP batch job with

sample JCL provided in member DSNTEJ1S of the SDSNSAMP library), refer toDB2 UDB

for z/OS Version 8 Administration Guide, SC18-7413.