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 

  • + 1
  • 0
  • 284
  • 1
  • 2


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.

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]

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.