unique or null 1

[Thorsten3]

Is it possible to create a table with a unique constrain on a column, but allow null values?
I want to make sure that a new value which is inserted into the table is not present already except if it is null.

 

[barbarul2001]

If you try to enforce unique constraint you could use NULL value, but only once. So, it is possible , but doesn't help you much. For your needs I recommend a trigger or to enforce integrity on the application level. senior rdbms specialist

 

[NVSbe]

Barbarul:
Doesn't ANSI SQL consider each NULL value to be different from the other? So, if the UNIQUE constraint was used, wouldn't it automatically allow multiple NULL Values? --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[r937]

according to the SQL standard, multiple NULL values are indeed allowed in a column with a UNIQUE index

however, not every database allows it

see
Multiple unique NULLs, revisited

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid489718_tax285649,00.html

rudy

 

[Thorsten3]

Thanks for your help, unfortunatly DB2 does not allow multiple nulls in a unique columns, I guess I let my application do the job.

db2 =>
create table test (
id integer not null,
name varchar(50),
primary key (id),
unique (name)
)
During SQL processing it returned:
SQL0542N "NAME" cannot be a column of a primary key or unique key because it can contain null values. SQLSTATE=42831
db2 =>
Thorsten