Article
Pravin Barton · May 12 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.

2
0 100
Discussion (5)2
Log in or sign up to continue

Are you sure you copied in the right SQL queries in your post? The ones you've included both try to update the EmailOptIn column to 0 where it's already set to 1, not where it's null. It seems to be they should be something more like "update Person set EmailOptIn = 0 where EmailOptIn is null".

You are very right, thank you David. I've fixed the post.

I follow a simple rule: first I change the data, and only then I put the constraints in the code. Not the other way around.

That's an even better solution, if your deployment strategy supports it.