By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,361 Members | 3,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,361 IT Pros & Developers. It's quick & easy.

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

P: n/a
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,
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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,


Nov 12 '05 #2

P: n/a
Thanks Blair for the update!

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bp**********@hanover.torolab.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,

Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.