Preventing duplicate key values

 

DB2 Universal Database™ for iSeries™ allows records with duplicate key values in a database file. However, you can prevent duplicate key values in your files.

For example, you can create a file where the key field is defined as the customer number field. In this case, you want the system to ensure that each record in the file has a unique customer number.

You can prevent duplicate key values in your files by specifying the UNIQUE keyword in data description specifications (DDS). With the UNIQUE keyword specified, a record cannot be entered or copied into a file if its key value is the same as the key value of a record already existing in the file. You can also use unique constraints to enforce the integrity of unique keys.

If records with duplicate key values already exist in a physical file, the associated logical file cannot have the UNIQUE keyword specified. If you try to create a logical file with the UNIQUE keyword specified, and the associated physical file contains duplicate key values, the logical file is not created. The system sends you a message stating this and sends you messages (as many as 20) indicating which records contain duplicate key values.

When the UNIQUE keyword is specified for a file, any record added to the file cannot have a key value that duplicates the key value of an existing record in the file, regardless of the file used to add the new record. For example, two logical files LF1 and LF2 are based on the physical file PF1. The UNIQUE keyword is specified for LF1. If you use LF2 to add a record to PF1, you cannot add the record if it causes a duplicate key value in LF1.

If any of the key fields allow null values, null values that are inserted into those fields might or might not cause duplicates depending on how the access path was defined when the file was created. The *INCNULL parameter of the UNIQUE keyword indicates that null values are included when determining whether duplicates exist in the unique access path. The *EXCNULL parameter indicates that null values are not included when determining whether duplicate values exist.

The following example shows the DDS for a logical file that requires unique key values:

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A* ORDER TRANSACTION LOGICAL FILE (ORDFILL)
     A                                      UNIQUE      A          R ORDHDR                    PFILE(ORDHDRP)
     A          K ORDER      A      A          R ORDDTL                    PFILE(ORDDTLP)
     A          K ORDER      A          K LINE      A

In this example, the contents of the key fields (the Order field for the ORDHDR record format, and the Order and Line fields for the ORDDTL record format) must be unique whether the record is added through the ORDHDRP file, the ORDDTLP file, or the logical file defined here. With the Line field specified as a second key field in the ORDDTL record format, the same value can exist in the Order key field in both physical files. Because the physical file ORDDTLP has two key fields and the physical file ORDHDRP has only one, the key values in the two files do not conflict.

 

Parent topic:

Using keyed sequence access paths for database files

 

Related concepts

Controlling the integrity of your database with constraints
DDS concepts