Home > DeveloperSection > Articles > Referential Integrity in SharePoint 2010

Referential Integrity in SharePoint 2010


SharePoint SharePoint 
Ratings:
0 Comment(s)
 7378  View(s)
Rate this:

Referential Integrity in SharePoint 2010

In this article I am going to explain how to implement referential integrity between lists in SharePoint Foundation 2010.

I have created two lists "Course (Parent list)" and "Student (Child list)". The parent list (Course) contains four columns i.e. Title, Course_ID, Course_Name and Duration and child list (Student) contains four columns i.e. Title, Student_ID, Student_Name and Course. In the "ChildList (Student)" I am creating a lookup column (Course) which will take values from "ParentList (Course) column (Course_ID)". 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: When an item in the parent list is deleted, cascade delete will delete all related items from the child lists.

Restrict Delete: It will prevent the deletion of an item in the parent list if it has one or more related items in the child list. When you try to delete an item in the parent list you will get the error message.

Here I am going to implement both Cascade and Restrict deletion:

1) Restrict Deletion:

·         Create a Course List (Parent List):
Site Actions
à More Options… à List à Custom List.

Referential Integrity in SharePoint 2010

Add columns and courses in the Course list as shown in the list below:

Referential Integrity in SharePoint 2010

·         Create a Student List (Child List):
Site Actions
à More Options… à List à Custom List.

Note: While creating a Course column in the Student list chooses the following option as show in the below figure:

Referential Integrity in SharePoint 2010

Referential Integrity in SharePoint 2010

Referential Integrity in SharePoint 2010

Referential Integrity in SharePoint 2010

Now when you attempt to delete the information present in the child list (Student) from the parent list (Course) it will give an error as shown in the below dialog box:

Referential Integrity in SharePoint 2010

1) Cascade Deletion:

In order to implement cascade deletion change the Course column relationship behaviors of the Student table into Cascade delete.

Student List à List Tools à List ribbon à List Settings.

Referential Integrity in SharePoint 2010

Change the column (Course) behavior to Cascade delete:

Referential Integrity in SharePoint 2010

Now when you attempt to delete the information from the parent list (Course) it will also remove the matching information from the child list (Student).

Here I am going to delete the course id from the Course list that is also present in the Student list:

Referential Integrity in SharePoint 2010

It will remove the matching course id present in the Student list:

Referential Integrity in SharePoint 2010

Thanks for reading this article. I think this will help you a lot.


Don't want to miss updates? Please click the below button!

Follow MindStick