Hello all,

               I would like to share the Foreign Key setup I recently used for my project and found it is very useful.  When asking around, I realized that there are some developers out there, who would like more information on explicit foreign key setup, so I would like to briefly discuss explicit foreign key setup and give a quick sample on how to set it up

               In some cases, we may consider using explicit Foreign Keys when we have projects, which contain multiple classes, with properties, which depend on one another and specifically, when deleting/updating a record in the class should effect / not effect the other class corresponding row.  Sometimes, we need to have ability to not allow the delete/update, if there are data already present in the corresponding row’s property defined with foreign key, and sometimes, it's the opposite - we need an ability to delete one row and this in turn will have to delete all its foreign key related rows. 

               From our documentation on foreign keys we see that the setup is fairly straight forward, so I am just going to quickly summarize it below:

ForeignKey name(Property) References referenced_class(ref_index) [ keyword_list ];

• Name is required field and has to be unique within class and can be any valid string.
• Property – specifies the property or properties what are constrained by this foreign keys, typically one.
• referenced_class - the class to which the foreign key points.
• ref_index is optional and will default to ref row Ids
• keyword_list

1. [ OnDelete ] - To specify what happens in the current table when a record is deleted in the foreign table = options

1. noaction (default) — When an attempt is made to delete a record in the referenced table, the attempt fails. This option is working if you do not define OnDelete.
2. cascade — When a record is deleted in the foreign table, the referencing record in this table is also deleted.
3. setdefault — When a record is deleted in the foreign table, the referencing record in this table is set to its default value.
4. setnull — When a record is deleted in the foreign table, the referencing record in this table is set to null.

2. [ OnUpdate ] - OnUpdate says what to do in the class containing the foreign key when the property/properties with the referenced unique index are updated in the referenced table  It has similar options as OnDelete
3. [ NoCheck ]; The NoCheck keyword suppresses the checking of the foreign key constraint (in other words, it specifies that the foreign key constraint never be checked).
4. [ SqlName = alternate_name ]; alternate name for this foreign key when referred to via SQL.
5. [ Internal ] = which, if set will not be part of class documentation.

One thing we have to remember is Index is needed in order for the Foreign key to work in timely fashion.   

My Example:

Here, I created a very simple example just to illustrate the use of foreign keys, when the properties are linked within a same class as well as between multiple classes, using [ OnDelete = cascade ] and [ OnDelete = noaction ] (which is default, so it will compile without showing Keyword at all)

If this can be helpful to anyone – great.

Here are my classes:

Class Test.Item Extends %Persistent
{Property productName As %String;
Property Seasonal As Test.Item;
ForeignKey key1(Seasonal) References Test.Item();
Index Seasonal On Seasonal;}

Class Test.Sale Extends %Persistent
{Property newSaleItem As Test.Item;
ForeignKey key2(newSaleItem) References Test.Item() [ OnDelete = cascade ];
Index newSaleItem On newSaleItem;
Property SoldItem As Test.Item;
ForeignKey key3(SoldItem) References Test.Item();
Index SoldItem On SoldItem;}

Class Test.Driver
{
      ClassMethod Run()
      {
      For i=1:1:3 {
                               Write !,$Case(i,
                                              1:"Demonstrating deleting Item record and using Foreign Key with [ OnDelete = cascade ] to show linked sale record also being deleted because of cascade",
                                              2:"Trying to delete an Item which has been sold and there is a Foreign Key set as [ OnDelete = noaction ] to show Foreign Key constraint failed upon DELETE against different class",
                                              3:"Demonstrating trying to delete an Item, which has been set as seasonal and using Foreign Key [ OnDelete = noaction ] to show Foreign Key constraint failed upon DELETE against the same class"
                               )
                              
                               Do ##class(Test.Sale).%KillExtent()
                               Do ##class(Test.Item).%KillExtent()
                              
                               Set itemRef = ##class(Test.Item).%New()
                               Set itemRef.productName = "Apple"
                               Set Sale = ##class(Test.Sale).%New()
                               Set Sale.newSaleItem = itemRef
                              
                               Set sc = Sale.%Save()
                               If $$$ISERR(sc) { Write !,$System.Status.GetErrorText(sc) }
                                             
                               Set itemRef2 = ##class(Test.Item).%New()
                               Set itemRef2.productName = "Pear"
                               Set Sale2 = ##class(Test.Sale).%New()
                               Set Sale2.SoldItem = itemRef2
                              
                               Set sc = Sale2.%Save()
                               If $$$ISERR(sc) { Write !,$System.Status.GetErrorText(sc) }
                                             
                               Set itemRef = ##class(Test.Item).%New()
                               Set itemRef.productName = "Watermelon"
                               Set sc = itemRef.%Save()
                               Set itemRef2 = ##class(Test.Item).%New()
                               Set itemRef2.Seasonal = itemRef
                               Set sc = itemRef2.%Save()
                                             
                               Write !,"***BEFORE***" 
                               Write !,"Test.Item" ,! Set query = "SELECT * FROM Test.Item" Do ##class(%SQL.Statement).%ExecDirect(,query).%Display() Write !
                               Write !,"Test.Sale" ,! Set query = "SELECT * FROM Test.Sale" Do ##class(%SQL.Statement).%ExecDirect(,query).%Display() Write !
 
                               Set sc = $Case(i,
                                                             1:##class(Test.Item).%DeleteId(1),
                                                             2:##class(Test.Item).%DeleteId(2),
                                                             3:##class(Test.Item).%DeleteId(3))
                               If $$$ISERR(sc) {
                                              Write !,$System.Status.GetErrorText(sc)
                               }
                              
                               Write !,"***AFTER***" 
                               Write !,"Test.Item" ,! Set query = "SELECT * FROM Test.Item" Do ##class(%SQL.Statement).%ExecDirect(,query).%Display() Write !
                               Write !,"Test.Sale" ,! Set query = "SELECT * FROM Test.Sale" Do ##class(%SQL.Statement).%ExecDirect(,query).%Display() Write !
               }
}