DO NOT REPLY [Bug 20104] New: - db2 ddl - unique constraints with nullable columns

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://nagoya.apache.org/bugzilla/show_bug.cgi?id=20104>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=20104

db2 ddl  - unique constraints with nullable columns

           Summary: db2 ddl  - unique constraints with nullable columns
           Product: Jetspeed
           Version: 1.4b4-dev / CVS
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: Blocker
          Priority: Other
         Component: Database
        AssignedTo: jetspeed-dev@jakarta.apache.org
        ReportedBy: hautekej@systemat.be


DDL Problems with DB2 UDB 7.2 FP8

CREATE TABLE JETSPEED_USER_PROFILE (
 PSML_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
 USER_NAME VARCHAR(32) NOT NULL,
 MEDIA_TYPE VARCHAR(99),
 LANGUAGE VARCHAR(2),
 COUNTRY VARCHAR(2),
 PAGE VARCHAR(99),
 PROFILE CHAR(99) FOR BIT DATA,
 UNIQUE (USER_NAME,MEDIA_TYPE,LANGUAGE,COUNTRY,PAGE)
);

-- same problem in
-- JETSPEED_GROUP_PROFILE
-- JETSPEED_ROLE_PROFILE

Apparently DB2 v7 doesn't accept NULLABLE colums in UNIQUE constraints
(SQL0542N, sqlstate 42831)

3 Possible solutions
1) change the parttaking columns in NOT NULL, ... may block (partial) INSERTs at
runtime ? 
2) remove the unique constraint, ... may block if code depends on uniqueness
3) change UNIQUE constraint to INDEX ... same prob as 2)

Please advise ...

---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org