[DERBY-3330] provide support for unique constraint over keys that include one or more nullable columns.

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.4.1.3
• Fix Version/s: 10.4.1.3
• Component/s: SQL, Store
• Environment: all
• Issue & fix info: Release Note Needed
• Assignee: Anurag Aggarwal
• Reporter: Anurag Aggarwal
• Created: 17/Jan/08 19:41
• Updated: 25/Nov/13 20:30
• Resolved: 10/Oct/08 17:31

Description

Allow unique constraint over keys which include one or more nullable fields. Prior to this change Derby only supported unique constraints on keys that included no nullable columns. The new constraint will allow unlimited inserts of any key with one more null columns, but will limit insert of keys with no null columns to 1 unique value per table.

There is no change to existing or newly created unique indexes on null columns (as opposed to unique constraints on null columns). Also there is no change to existing or newly created constraints on keys with no nullable columns.

Issue Links

• is related to: DERBY-3523 sql states (X0Y63, X0Y63, X0Y63.S) related to nulls in unique constraints are associated with wrong message texts
• is related to: DERBY-2212 Add "Unique where not null" to create index
• relates to: DERBY-3502 Unique Constraint's backing index when shared with existing indexes doesn't behave as expected

Sub-Tasks

• Allow removing not null from collumns particpating in unique constraint.: Closed
• write test cases to test locking during tree operation: Closed
• update unique constraint sections of refrence manual: Closed