15.9.2 Steps to take when the uniqueness constraint is violated (when the KFAA61205-W message is output)

When the adbimport or adbidxrebuild command is executed on a table for which a unique index is defined, processing of each command continues even if there are duplicate key values and the uniqueness constraint is violated. When the adbimport or adbidxrebuild command terminates, the KFAA61205-W message is output, indicating violation of the uniqueness constraint.

A unique index that violates the uniqueness constraint is treated as a B-tree index that does not have the uniqueness constraint. This status is called uniqueness constraint violation. When uniqueness constraint violation occurs, the search efficiency might be inferior to a case in which the uniqueness constraint is satisfied.

Organization of this subsection

(1) Checking whether a uniqueness constraint violation has occurred
(2) Steps to take when the uniqueness constraint is violated

(1) Checking whether a uniqueness constraint violation has occurred

Besides checking the KFAA61205-W message, you can execute the adbdbstatus command to check whether a unique index is in uniqueness constraint violation. For details, see 10.9.3 Checking the status and usage of a B-tree index.

To Page Top

(2) Steps to take when the uniqueness constraint is violated

To release a unique index from uniqueness constraint violation, take the steps described in the following procedure:

Search for duplicate key values

Search for duplicate key values by executing the SELECT statement on the table for which the target unique index is defined. The following shows a specification example of the SELECT statement.

■ SELECT statement specification example

This example searches for key values that violate the uniqueness constraint when unique index IX1 is defined, with columns C1 and C2 of table T1 as indexed columns.
```
SELECT "C1","C2" FROM "T1"
   WHERE "C1" IS NOT NULL AND "C2" IS NOT NULL GROUP BY "C1","C2"
     HAVING COUNT(*) >= 2
```
Delete rows that have duplicate key values.

Use the DELETE statement to delete rows with key values that violate the uniqueness constraint.

For details about the DELETE statement, see DELETE (delete rows) in Data Manipulation SQL in the manual HADB SQL Reference.
Rebuild a B-tree index.

Execute the adbidxrebuild command to rebuild a B-tree index. When the adbidxrebuild command finishes, you can cancel uniqueness constraint violation.

For details about the adbidxrebuild command, see adbidxrebuild (Rebuild Indexes) in the manual HADB Command Reference.

Note

Uniqueness constraint violation is also canceled when either of the following operations is used to delete all data from a table for which a unique index is defined that violates the uniqueness constraint:

Executing the adbimport command with the -d option (creation mode) specified
Executing the TRUNCATE TABLE statement

To Page Top