Can ESRI give its users the ability to alter the behavior of simple relationship class, with regards to setting the Foreign Key value to NULL upon deleting an origin object? I find this behavior very annoying to say the least. I would prefer not to have the foreign key value changed to NULL, and thus keep its original value. Here's a scenario which would support my idea:
You have a simple relationship class created in which the origin is a polygon feature class and the destination is a table. For the sake of this example, the Primary and Foreign keys are FEATURE_ID. The feature class contains 1 polygon (FEATURE_ID = 1) and the related table contains 1 record ( FEATURE_ID =1). I require to split my polygon in half, resulting in 2 polygons (both with FEATURE_ID = 1). At this point, each polygon has a related record in the table. Great! If I proceed to delete one of the two polygons (resulting in 1 polygon with FEATURE_ID = 1) the current behavior of the simple relationship class will actually change the FEATURE_ID in the related table to NULL, thereby removing the link between the resulting polygon and the table. This is a huge problem for us. We run into this all the time.
The solution to this problem would be to allow the users to disable this behavior. We need to maintain the original Foreign Key value.
ArcGIS 10 Desktop help describes a simple relationship class and its behavior as:
"In a simple relationship, related objects can exist independently of each other. For example, in a railroad network, you may have railroad crossings that have one or more related signal lamps. However, a railroad crossing can exist without a signal lamp, and signal lamps exist on the railroad network where there are no railroad crossings.
When you delete an origin object in a simple relationship, the foreign key field value for the matching destination object is set to Null. This foreign key behavior was designed to maintain referential integrity between features. If the origin feature is deleted, then the value in the foreign key is no longer relating that row to a feature in the origin and, as a result, the foreign key value is no longer required and is set to Null. The sole purpose of the foreign key is to maintain a relationship between the destination object and the related origin object. If there is no origin feature with the matching primary key value, then there is no reason to maintain the foreign key value. If you want to relate the same destination feature to a new or different origin feature in the future, the FK field can be updated from Null to the new FK value."