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.
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.
@Pravin Barton ... Indeed very non-intuitive - thank you for sharing what you learned!
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.
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!
Craig
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.