Referential Integrity in SharePoint 2010

  In this article I am showing you a new feature in SharePoint 2010 called referential integrity.

 

As we know SharePoint 2007 support lookup field. But SharePoint 2010 supports referential integrity also. For example we can have two lists for our company named Employee Details and Department. We can create a referential integrity between department fields in employee details and department name in Department list. Most of us are familiar with SQL server there we have cascade delete and Restricted delete are possible in SharePoint 2010 we have that flexibility. For those who doesn't know what is cascade delete and restricted delete.

• Cascade Delete - when you delete a row in the parent table, SharePoint also deletes any rows associated with that row in the child table
   
• Restrict Delete - SharePoint will allow any row to be deleted from the parent list only if there is no associations for that item with any other item on the child list
  
  For the demo I have created two lists
  
  
  1. Employee Details
  2. Department
   
• In Department I have only one column named Title that contains different department names
   
• In Employee details I have created a look up column from the Department list as shown below
  
  
   
• Now you have one option below to set relation I have selected Restrict delete
  
  
  
   
• Once we set this. I have created an item with some value from department list. Then I tried to delete the value I selected from department list I got the below error
  
  
  
   
• So if an item in parent list is associated to items in the child list, then this item in parent list cannot be deleted unless its association with all the items from the child list is removed.