ArcGIS Relationships on Non-Null Fields

Background

Typically a relationship between an ArcGIS feature class and another feature class or table is established by a “primary key” field on the origin table and a “foreign key” field on the destination table. The life span of related records in these classes can be linked or un-linked. If the destination table record cannot logically exist without its origin table record then we create a “composite” relationship, otherwise we create a simple relationship. In this post we’ll talk about ways of defining simple relationships, and specifically simple relationships where the foreign key field is non-null, and impacts on the user editing experience.

Simple Relationship Example

First, let’s look at a condition where we have service lines related to a separate table with service line attributes. There is a simple, one-to-one relationship between the service line feature and a row in the ServiceLineData table with the primary and foreign keys as fields named [ServiceLineNumber] in both classes. There are a number of reasons why this database organization may make sense – including the case where attributes in the ServiceLineData table are updated by a different work flow than the service line feature geometries. As illustrated below, this appears as expected.

One2One_101
When we start editing on this workspace and delete the service line feature, by design the foreign key field in the ServiceLineData record is nulled out. This is core ArcGIS product functionality and completely valid. The origin class feature no longer exists so keeping a value in a key field that would point to nothing would be misleading and erroneous.

One2One_102

When the Foreign Key Field is “Non-Null”

However, while the logic applied above is as desired for many conditions it’s not so for all. In a similar condition we might have the foreign key field in the ServiceLineData table defined as “not null”. This may be the case if that value is an important company identifier that defines the record to applications both inside and outside the Geodatabase. With a simple, one-to-one relationship where the foreign key field is defined as “not null” the result when attempting to delete the service line will be different – specifically, we’ll get an error message like this:

One2One_103

When ArcMap tries to set the foreign key field to <null> we encounter a database constraint violation and the edit is rejected.

While this error message is only informing the user of an as-designed result, it may not be the user experience we want.   So, what to do from here?  There are (at least) two options — excluding making the foreign key field nullable.  One is to ask users confronted with this case to first alter the value of the foreign key value in a slight way prior to the delete – thereby breaking the relationship to the origin feature and avoiding the error.

If this is more than what you’d like to ask users to take on, another option is to change the definition of the relationship. Rather than having primary and foreign keys defined as fields directly on the origin and destination tables we can create a relationship based on a “join table” that stores ObjectID values of related tables.  In this case only the join table is updated as relationships are created, altered or deleted – so there is no need to try to null a non-null field.

If using ArcCatalog to create the relationship choose the option to “Add Attributes” this will create a table that will hold values for primary and foreign keys – as well as any other attributes you may want.

One2One_005

So, going back to our service line and ServiceLineData classes we’ll see that we can even add the relationship “join” table to our map to see what’s happening. Clearly, the join table holds the ObjectIDs of the related features.

One2One_104

Now, finally, when we delete the service line with new relationship definition in place our service line feature is gone, but the related ServiceLineData record remains intact.

One2One_105
 

Summary

This post has addressed the case where we need an ArcGIS relationship to a destination class where the foreign key field needs to be non-null and used the case of a service line with a related table of service line data as an example. It may be the case that use of an attributed relationship that makes used of a join table can improve your user work flow.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>