#283 Multiple NULL value in UNIQUE key Field creates error

[Nihar Ranjan paital]

We know that if a field is both Unique and NOT NULL we can say it is Primary key field. That means if a field is only unique it can accept NULL value. But one NULL is not exactly same as another NULL. So we can enter more than one NULL values in a unique key field.

Error in tool:
When we enter NULL first time in the unique field it is passed.When another NULL is given it gives the following error.

Unique key violation
Error in inserting to index
Unable to insert index node for tuple 17e88034 0
Statement execute failed with error -21

CSQL>create table t1(f1 int,f2 int);
Statement Executed
CSQL>create index idx1 on t1(f1)unique;
Statement Executed
CSQL>insert into t1 values(1,2);
Statement Executed: Rows Affected = 1
CSQL>insert into t1 values(NULL,3);
Statement Executed: Rows Affected = 1
CSQL>select * from t1;
---------------------------------------------------------
f1 f2
---------------------------------------------------------
1 2
NULL 3
CSQL>insert into t1 values(NULL,6);
9680:3086264016:HashIndex.cxx:198:Unique key violation
9680:3086264016:TableImpl.cxx:387:Error in inserting to index
9680:3086264016:TableImpl.cxx:400:Unable to insert index node for tuple 17e88034 0
Statement execute failed with error -21
CSQL>

Expectation : The last statement should work properly.