· May 12, 2022 1m read

Making an existing property Required in a Persistent class

Say I have a persistent class in IRIS with an optional property EmailOptIn:

Class Person Extends %Persistent
Property Name As %String;
Property EmailOptIn As %Boolean;

I later realize that I'm doing a lot of null-checking on this property where I shouldn't need to. The solution is to make this a required property:

Class Person Extends %Persistent
Property Name As %String;
Property EmailOptIn As %Boolean [ Required ];

When I make this change I'll need to update all the existing data to set a reasonable default where it is null. Otherwise I'm in a bad state where updating the Name of a Person will fail if their EmailOptIn is null. I might expect to fix this in SQL as follows:

&sql(update Person set EmailOptIn = 0 where EmailOptIn is null) 

However, running this query will leave me with no rows affected. I can also use a SELECT statement to verify that the WHERE condition matches no rows. I interpret this as SQL assuming none of the entries are null since the property is required. The workaround is a little unintuitive. Even though no index exists, using the %NOINDEX keyword in the condition forces the update to actually check for null entries.

&sql(update Person set EmailOptIn = 0 where %NOINDEX EmailOptIn is null) 

This edge case tripped me up recently. Hopefully this article can save another developer a little time.

Discussion (7)2
Log in or sign up to continue

Hello Pravin,

Thank you for posting this article. It was easy to read, and I think it will be very helpful.

I have a question that I'm wondering if you would be able to help me with. I am trying to add relationships between tables with existing data. The relationships are required. Once we add them, we are no longer able to see data in the child tables which I believe is to be expected. I was hoping your suggestion to use %NOINDEX in the Select statement would display the data so we could then add the required relationships. Unfortunately, %NOINDEX doesn't seem to be working in this instance.

I was just wondering if you had any other suggestions on how to return data that will not display even though it is present?

Thank you!


If you are defining a Parent-Child Relationship with Default Storage the global structure of the child with change from ^child(id) to ^child(parentid,id) so you will not see any of the old data, no hint will fix that issue, sorry.

If your current child data is valid and you want to keep it I would suggest defining a Foreign Key constraint with Cascade on Delete.  This will keep the global structure the same but let you enforce your relationship.