Unique constraint or index as in Oracle and "alter table alter column"

[Prince Kumar]

Is there any way I can define an Unique constraint or unique index
which allows more than one null values for the same column combination
in DB2?

ie, If my index is defined on (col3, col4) where both columns allow
nulls, I want col3 + col4 to be unique, if one or both the columns
have values. If both columns have nulls, it should allow more than one
such rows.

ex,

1 null -> unique
2 null -> unique
2 null -> duplicate --> reject it
null null -> unique
null null -> unique --> accept it (both values are nulls)
null 1 -> unique.

Also, is there any way to alter a INT/DEC (number) column from
NULLABLE to NOT NULLABLE and vice versa?

thanks,

[Blair Adamache]

Given:

create table testuni (col3 int, col4 int);

create unique index test1 on testuni (col3, col4);

....these two statements will fail:

2nd iteration of: 2 null -> duplicate --> reject it - as you said
2nd iteration of: null null -> duplicate - rejected with SQL0803N
"One or more values in the INSERT statement, UPDATE ..."

After a table is created, you can only turn nullability on/off with a
constraint:

alter table testuni add constraint testnull check (col3 is not null)

This is not exactly the same as defining a column not null (for example,
the constraint above will not a primary key to defined against col3)

Prince Kumar wrote:

Is there any way I can define an Unique constraint or unique index
which allows more than one null values for the same column combination
in DB2?

ie, If my index is defined on (col3, col4) where both columns allow
nulls, I want col3 + col4 to be unique, if one or both the columns
have values. If both columns have nulls, it should allow more than one
such rows.

ex,

1 null -> unique
2 null -> unique
2 null -> duplicate --> reject it
null null -> unique
null null -> unique --> accept it (both values are nulls)
null 1 -> unique.

Also, is there any way to alter a INT/DEC (number) column from
NULLABLE to NOT NULLABLE and vice versa?

thanks,

[Prince Kumar]

Thanks Blair for the update!

Blair Adamache <ba*******@2muc hspam.yahoo.com > wrote in message news:<bp******* ***@hanover.tor olab.ibm.com>.. .

Given:

create table testuni (col3 int, col4 int);

create unique index test1 on testuni (col3, col4);

...these two statements will fail:

2nd iteration of: 2 null -> duplicate --> reject it - as you said
2nd iteration of: null null -> duplicate - rejected with SQL0803N
"One or more values in the INSERT statement, UPDATE ..."

After a table is created, you can only turn nullability on/off with a
constraint:

alter table testuni add constraint testnull check (col3 is not null)

This is not exactly the same as defining a column not null (for example,
the constraint above will not a primary key to defined against col3)

Prince Kumar wrote:

Is there any way I can define an Unique constraint or unique index
which allows more than one null values for the same column combination
in DB2?

ie, If my index is defined on (col3, col4) where both columns allow
nulls, I want col3 + col4 to be unique, if one or both the columns
have values. If both columns have nulls, it should allow more than one
such rows.

ex,

1 null -> unique
2 null -> unique
2 null -> duplicate --> reject it
null null -> unique
null null -> unique --> accept it (both values are nulls)
null 1 -> unique.

Also, is there any way to alter a INT/DEC (number) column from
NULLABLE to NOT NULLABLE and vice versa?

thanks,

[AK]

For example, in a police database, column VEHICLE.VIN could be NULL
(which makes sence, let's consider a situation when witnesses describe
a hit-and-run accident, then
number plate and/or VIN may be not known). How to implement a
requirement that if VIN is known, it has to be unique?

I've created a separate entity for VIN and removed VIN column from
VEHICLE table
CREATE TABLE VEHICLE(VEHICLE _ID INT NOT NULL PRIMARY KEY, ... );
CREATE TABLE VIN(VEHICLE_ID INT NOT NULL PRIMARY KEY, VIN VARCHAR(30)
UNIQUE);

The technique is called inheritance of primary key