community.borland.com

Article #26665: UNIQUE indexes don't allow NULLs

Q. I've done the following:

CREATE TABLE T1(A Int, B Int);
CREATE UNIQUE INDEX I1 ON T1 (A, B);
INSERT INTO T1 VALUES (1, NULL);

This results in the following error:

Statement failed, SQLCODE = -803

attempt to store duplicate value (visible to active transactions) in unique index "T1"
-null segment of UNIQUE KEY

What is the cause?

A. Unique indexes do not allow NULLs even if they have multiple fields. If you require NULL data in a multifield index, there are two approaches you can take to solve this problem:

Last Modified: 18-JAN-01