RDBMS Features in SharePoint 2010\13 : Referential Integrity,Unique Keys and Joins

SharePoint lists are, by and large, similar to database tables in structure and behavior. Like Tables, Lists support different data types and can have triggers (event receivers) that fire on events such as creating, updating or deleting items. Also, lists can be configured to filter, sort or group items based on column values.

Also, SharePoint lists support the concept of view like in database and can be queried by CAML. The columns in the list can be indexed for better performance while querying.

In SharePoint 2010, the lists are much more enhanced to incorporate important aspects of RDBMS like :

  1.  Referential Integrity
  2.  Unique Keys
  3.  Joins

1.  Referential Integrity with improved lookup columns

In previous versions of SharePoint (2003, 2007), the  lists lacked an important aspect of RDBMS – Referential integrity- that is a measure of data consistency in a database. If you have a lookup between two lists and you want to have Referential Integrity, previous SP versions won’t provide it. The lookup columns could be used just to get their data from other list columns.

Now, SharePoint 2010 provides the mechanism to enforce Referential integrity in our data model. Also, it can block or cascade your deletes between lists automatically.

Just like foreign key constraints in RDBMS, SharePoint 2010 allows you to configure restrict delete and cascade delete rules on lookup column relationships.

Cascade delete : When we delete a list item, SharePoint automatically delete all items that have references to that item by look up column. It can be used to enforce parent-child relationships.

Restrict delete : With this rule, SharePoint don’t allow you to delete a list item that is referenced by a lookup column in another list. It can be used to enforce peer-to-peer relationships.

[Note: SharePoint is not going to be a replacement for SQL Server with this functionality. If you have a very complex data model, SQL Server is an obvious choice]

An additional improvement on lookup columns is that now we can retrieve more columns(Projected Columns) from the related list.

Configuring the Delete Behavior on a Lookup Field 

We can configure list relationship rules through the user interface when we create look up column.

Look Up Column Delete Behavior

We can also programmatically configure delete behavior in a feature receiver class. Since the list relationship is specific to individual list instances, we cannot configure it declaratively in column definitions or content types.Programmatically setting the delete behavior can be useful if you use features to deploy the lists and columns.

SPFieldLookup is class that represents lookup columns. This class has a RelationshipDeleteBehavior enum property that enables us to specify the delete behavior for the column.It can be set to one of the following SPRelationshipDeleteBehavior enumeration values:

  • SPRelationshipDeleteBehavior.Cascade. Setting this value deletes all items if the lookup column refers to an item that no longer exists in the related list.
  •  SPRelationshipDeleteBehavior.Restrict. Setting this value prohibit the users from deleting items in the related list if those items are referred to in the lookup column values.

For example, we can use below methods in a feature receiver class to restrict or cascade delete.

 
private void RestrictDelete(SPWeb web)
        {
            SPList list = web.Lists["MyList"];
            SPField field = list.Fields["TestNameLookUp"];
            SPFieldLookup fieldLookup = (SPFieldLookup)field;

            //For unique values of column in items
            fieldLookup.EnforceUniqueValues = true;
            //For indexing column
            fieldLookup.Indexed = true;

            fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
            fieldLookup.Update();

        }

     private void CascadeDelete(SPWeb web)
        {
            SPList list = web.Lists["MyList"];
            SPField field = list.Fields["TestNameLookUp"];
            SPFieldLookup fieldLookup = (SPFieldLookup)field;
            //For unique values of column in items
            fieldLookup.EnforceUniqueValues = true;
            //For indexing column
            fieldLookup.Indexed = true;         

            fieldLookup.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Cascade;
            fieldLookup.Update();

        }

Programmatically Find Lookups and Related Lists

We can use the GetRelatedFields method to return a SPRelatedFieldCollection collection. We can retrieve properties, such as the LookupList that the column  is related to,  the relationship behavior when some item is deleted from the list and also some other useful information.

Below is the example

private void GetRelatedColumnsAndListsInfo(SPWeb web)
        {
            SPList list = web.Lists["MyCustomersList"];

            //Get related columns
            SPRelatedFieldCollection relatedFields = list.GetRelatedFields();

            foreach (SPRelatedField relatedField in relatedFields)
            {
                //Get Lookup list
                SPList relatedList = relatedField.LookupList;
                Console.WriteLine(relatedField.ListId + " " +   relatedField.FieldId);
                Console.WriteLine("List Name: " + relatedList.Title + " Relationship Behavior: " + relatedField.RelationshipDeleteBehavior.ToString());

            }

        }

Controlling the cascade delete limits and time outs

At web appilcation level, We can have control over the Cascade deletes  using SPWebApplication.CascadeDeleteMaximumItemLimit and SPWebApplication.CascadeDeleteTimeoutMultiplier.

  • CascadeDeleteMaximumItemLimit allows you to specify the maximum  number of cascaded items that can be deleted. By default, this value is 1000 items.
  •  CascadeDeleteTimeoutMultiplier  allows you to specify the timeout(in secs)  for the operation. The default value is 120 seconds.
 private void SetCascadeDeletes(SPWebApplication webApp)
        {
            //sets max cascade delete limit to 2000
            webApp.CascadeDeleteMaximumItemLimit = 2000;           

            //sets the time out to 5 mins
            webApp.CascadeDeleteTimeoutMultiplier = 300;

            webApp.Update();
        }

2.  Unique Keys (Columns)

SharePoint 2010  lists have the ability to ensure uniqueness for the values in your columns. As the unique column  is guaranteed to have a unique value, We can  it as an  index to make look ups faster.

Unique Column

3. List Joins

SharePoint lists support joins like database lists.  SharePoint can perform left and inner joins but not right joins.

  • By inner join, we can combine the values from the liststables.
  • By left join or left outer join , anything that appears in the left listtable  will be returned in the result set even if it does not exist in the right listtable.

We can perform a join between two lists on a lookup field by setting the Joins property on our SPQuery object with the join we want to perform.

Apart from the Joins property, we must also specify a value for the ProjectedFields property. This property gets other required columns from the lookup list. We can alias the column by using the Name attribute and specify the column name in the ShowField attribute. After getting results, we have to use the SPFieldLookupValue  to display the values for projected columns.

In the below example, we are joining on the Customers list, where the Customers.customer = Orders.Customer

private void PerformListJoin(SPWeb web)
        {

            SPList OrderList = web.Lists["Orders"];
            SPQuery CustomerQuery = new SPQuery();
            CustomerQuery.Joins =
            " <Join Type='INNER' ListAlias='Customers'> " +
            " <Eq> " +
            " <FieldRef Name='Customer' RefType='Id' /> " +
            " <FieldRef List='Customers' Name='ID' /> " +
            " </Eq> " +
            " </Join> ";

            StringBuilder ProjectedFields = new StringBuilder();
            ProjectedFields.Append(" <Field Name='CustomerTitle'  Type='Lookup' List='Customers' ShowField='Title' /> ");
            ProjectedFields.Append(" <Field Name='CustomerAddress'Type='Lookup' List='Customers' ShowField='CustomerNum' /> ");

            CustomerQuery.ProjectedFields = ProjectedFields.ToString();
            SPListItemCollection Results = OrderList.GetItems(CustomerQuery);

            foreach (SPListItem Result in Results)
            {
                SPFieldLookupValue CustomerTitle = new SPFieldLookupValue(Result["CustomerTitle"].ToString());
                SPFieldLookupValue CustomerAddress = new SPFieldLookupValue(Result["CustomerAddress"].ToString());

                Console.WriteLine(Result.Title + " " + CustomerTitle.LookupValue + "  " + CustomerAddress.LookupValue);
            }
        }

Technorati Tags : , ,

5 Responses to “RDBMS Features in SharePoint 2010\13 : Referential Integrity,Unique Keys and Joins”
  1. Dave says:

    Great Information Amit!

    By the way, you missed one more RDBMS feature – ‘Throttling’ :)

  2. Rajesh Singh says:

    This is one of the best articles so far I have read online. No crap, just useful information. Very well presented. Thanks for sharing with us. Its really helpful for beginner as well as developer.

    Thanks

  3. zuke says:

    I agree – very clear.

    Cheers

  4. Yasir says:

    nice article very helpful keep it up :)

Leave a Reply

Subscribe

Get every post delivered to your inbox via FeedBurner :

© 2010-2013 Extreme Sharepoint | The content is copyrighted to Amit Kumawat and may not be reproduced on other websites.