Indexing null value

Question

Question

Alexandr Ladoshkin · Nov 23, 2017

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

Sergei Madiev · Nov 24, 2017

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.

1 0

score 2 · Answer 1 · 2017-11-23T18:26:48-05:00

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.

score 0 · Answer 2 · 2017-11-24T10:12:33-05:00

ok.
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]