Referential Integrity in SharePoint 2010


In this article we will be seeing a new feature called Referential Integrity in SharePoint 2010.

I have created two lists "ParentList" and "ChildList". In the "ChildList" I am creating a lookup column which will take values from "ParentList". When you create a lookup column you can see "Relationship" where you can maintain referential integrity enforcing the relationships defined by lookup columns. Just like foreign key constraints in a relational database, in SharePoint 2010 you can configure restrict delete and cascade delete rules on lookup column relationships:

  • Cascade delete rules: Delete items that reference a record when you delete that record.
  • Restrict delete rules: Will prevent you from deleting a record that is referenced by a lookup column in another list.

Cascade delete:

When an item in the target list is deleted, cascade delete will delete all related items in the list. For example in the parent list I have the following items

Referential1.gif

And in the child list I have the following items

Referential2.gif

I have chosen "Cascade delete" and when I try to delete an item "Java" from the "ParentList" a message will pop up

Referential3.gif

It will delete all the items related to the list.

Once you delete the "Java" item in the parent list, the child list looks like the following.

Referential4.gif

Restrict delete:

It will prevent the deletion of an item in the target list if it has one or more related items in the list.

When you try to delete an item in the parent list you will be getting the following error message

Referential5.gif