· Nov 23, 2017

Indexing null value

Dear community!

I have problem with index NULL value.  Unique index doesn't work for this case.  If I use insert and one of parameter is "NULL". Message of constraint doesn't appear and row is inserted into table successfully.  How Can I use index with NULL?

Class TestClassIndx Extends %Persistent

Property name As %String [ Private ];

Property age As %String [ Private ];

Property country As %String [ Private ];

Index IndextestUniq On (name, age, country) [ Unique ];


INSERT INTO TestClassIndx (name,age,country) VALUES ('1','2',NULL)

Best Regards 

Discussion (3)0
Log in or sign up to continue

We solved it by using calculated property for the desired unique index:
 s {calcPropForIndex}=$s({originalProp}='':someUnusedValue,1:{originalProp})

The suggestion of Robert to use ''(empty string) instead of NULL can cause problem using object script, obj.porp="" is equivalent to set NULL in SQL.
Also FYI, there is a parameter INDEXNULLMARKER in %Library.DataType class, but it is crazy to override every type to have it in unique Index.

In my opinion using of NULL in Cache is really confusing.