Hi ,

Is it necessary that a unique constraint column should have the constraint NOT NULL.

Bcoz, I got this errro when executing the following :

CREATE TABLE T_Person (
USERID VARCHAR2(64), EMPLOYEENUMBER VARCHAR2(256),
CONSTRAINT PERSON_A3 UNIQUE (USERID)
);

"USERID" cannot be a column of a primary key or unique key because
it can contain null values. SQLSTATE=42831

Please explain how can I modify the above DDl.

I think the message is pretty clear. Check the SQL Reference for details. When a unique constraint is created, DB2 will create a unique index on that column(s) if one does not already exist.

You can create a UNIQUE WHERE NOT NULL index on that column. In this case NULL values are accepted.

UNIQUE WHERE NOT NULL indexes are allowed in DB2 for OS/390 and z/OS, but I don't believe they are allowed in DB2 for UNIX, Linux, and Windows.

In DB2 for UNIX, Linux, and Windows if can create a unique index on a column(s) which are nullable, nulls are treated like any other value and only one null value may exist in the table for the index key.

When creating an unique constraint, the columns must be defined as NOT NULL. This applies to DB2 for OS/390 and z/OS and DB2 for UNIX, Linux, and Windows.