Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: About Unique Constraint - Urgent Plz !

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Location
    Turkey
    Posts
    1
    You can create a UNIQUE WHERE NOT NULL index on that column. In this case NULL values are accepted.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •