Alexandr Ladoshkin · 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 

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

I think there is a basic misunderstanding of what NULL means in SQL.

NULL in SQL means unknown / undefined value / any value
Which is to my understanding a clear contradiction to uniqueness.

If you assign yourself something indicating NO VALUE ( e.g '') then it works but it isn't NULL anymore in the sense of SQL.

You followed the direction: that you mark the "unavailable" value by a UNIQUE value.
So for SQL it's NOT NULL.
I agree with you that NullString (COS) => String of length 0 and NULL(SQL) is confusing 
[since invention of SQL in the late 60ties]

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.