Relationship-Delete-Behavior in SharePoint List

A Couple months ago, I got problem with lookup list in SharePoint 2010. Most of you already know that lookup column could facilitate our list having relationship with other. Basically, this functionality allows us to use join statements between lists. By default, SharePoint sets maximum 8 columns for lookup list column threshold in regards to considering performance effects (see List View Threshold).

Standard codes (file Schema.xml) for performing list instance of lookup field as follow :

<Field ID="{D5201622-1511-4858-9353-93B94AF03579}" Type="Lookup" Name="FamilyIDLookup" DisplayName="Family ID" List="Lists/FamilyList" ShowField="ID" RelationshipDeleteBehavior="Restrict" Indexed="TRUE"/>      

I create a column with name FamilyIDLookup and its value are ID from table Family List. Index is True, it’s recommended since has possibility to increase performance while executing query within lookup columns.

Enforcing List Relationships

As similar as relational database, using lookup column in SharePoint list allow us to configure delete behavior within columns:

1. Cascade delete are parent-child relationship. When we delete one lookup item, then its reference item on other table will be deleted as well.
2. Restrict delete will prevent deleting item which has reference to other table.

What cause me pain was after solution being deployed to server, some list definitions are not set for any relationship-delete-behavior. Rechecking all list definitions within solution was a huge work since it contained many lists. So I came with one idea to enforcing relationship for list in deployed server. Creating one webpart for handling that task is pretty simple and I could set cascade or restrict delete-behavior for list, even it had been deployed. Here the codes:

// Instance variable to cater lookup GUID field
List<string> lookupGuids = new List<string>();

using( SPSite site = new SPSite( "http://mysite/") )
{
   using( SPWeb web = site.OpenWeb() )
   {
      // get all lookup field from list
     SPList list = web.Lists.TryGetList( "MyList" );
     SPFieldCollection fieldCol = list.Fields;
		
     foreach( SPField item in fieldCol )
     {
        // Filter the field from system fields
        if( ( !item.FromBaseType ) || ( item.StaticName.Equals( "Title" ) ) )
        {
            SPField field = list.Fields[ item.Id ];
            SPFieldLookup fieldLookup = field as SPFieldLookup;
	
            // Add lookup field only
            if( fieldLookup != null )
            {
                lookupGuids.Add( listItem );
            }
         }
      }
   }
}

// Enforce relationship-delete-behaviour
foreach( string id in lookupGuids )
{
    SPField field = list.Fields[ new Guid( id ) ];
    SPFieldLookup fieldLookup = field as SPFieldLookup;
    if( fieldLookup != null )
    {
       // set Index to true
       fieldLookup.Indexed = true;
		
       // check relationship whether is unable, it has to enable before set delete-behaviour
       if( !fieldLookup.IsRelationship )
      {
          List<string> lists = fieldLookup.GetDependentLookupInternalNames();
          fieldLookup.IsRelationship = true;
          fieldLookup.Update();
          list.Update();
       }

       // set restrict behaviour
       fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict; // Or SPRelationshipDeleteBehavior.Cascade
       fieldLookup.Update();                                        
    }
}

See more from msdn: List Relationship.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s