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.

Get Data Integrity with DB2 for z/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.