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?
{
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
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.
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]
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.