[JS1-320] [FIX] db2 ddl - unique constraints with nullable columns

• Type: Bug
• Status: Open
• Resolution: Unresolved
• Affects Version/s: 1.4b4-dev / CVS
• Component/s: Database
• Environment: Operating System: All; Platform: All
• Bugzilla Id: 20104
• Assignee: Unassigned
• Reporter: Jeroen Hautekeete
• Created: 21/May/03 15:48
• Updated: 16/Feb/11 21:04

Description

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 ...