Article
Alex Kogan · Nov 15, 2020 11m read

Using explicit Foreign Key simple example

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.
  1. [ 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
  2. [ 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).
  3. [ SqlName = alternate_name ]; alternate name for this foreign key when referred to via SQL.
  4. [ 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 !
               }
}

 

00
0 3 69 3

Replies

From my experience, foreign keys are really underrated/underused among ObjectScript developers. With relationships you don't need to worry about them, but really any time you have an object-valued property (not a relationship) there should almost certainly be a foreign key defined on it. (Same thing goes of course for non-object references to uniquely identifying fields in other tables.)

I agree with Tim but I'll take it one step further. Foreign keys are much more useful than relationships. After all, a relationship is simply a foreign key that maintains references to instances of the related class in memory. The projection of a relationship to SQL is simply as a foreign key. The set of related objects is simply populated using an SQL query. The problem with relationships is that they are extremely sticky and that can cause large numbers of objects to be inadvertently swizzled into memory. With foreign keys you have no in-memory model. That means with a foreign key you have to manage desired swizzling. Some view that as a problem, I view it as an advantage.

Another advantage of using foreign keys is that you can define multiple foreign keys using the same key component properties. No need to define a direct reference.

Creative minds might come up with a calculated property whose value is derived from the foreign key components, adding property methods to manipulate the related object/objects. This calculated property could be the direct reference. Perhaps transient would be better as a transient property also has instance memory allocated for it.

re: "creative minds" - been there, done that, should probably put it on the Open Exchange at some point... ;)