SharePoint 2010 Referential integrity - Using LookUp Column

There are two major enhancements in Lookup Columns for SharePoint 2010.

1. You can now display additional columns from the look-up list, along with the look up field data.
for e.g. If your Look-up List has Columns City, Zipcode and State, and if you select "City" to be displayed as Look-up column in your list, you would also have an option to display other information like Zipcode and State along with the City column in your view. You cannot however treat Zipcode and State as look-up columns, they are just additional information to the City you selected.


2. Implementing Referential integrity

When you create a Lookup column in SharePoint 2010, you would see an option (at the bottom) to define a Relationship. You are given two choices

1. Restrict Delete
2. Cascade Delete

Restrict Delete : Choosing this option would restrict the users from deleting an item in the column in the Look-up list, if the value is being used in some other lists. This means that if a value in the City column is used in our list, then it cannot be deleted from the look-up list.

Cascade Delete : If an Item\value in the City column in the look-up list is deleted, then all those items referencing that value (as look-up value) in other lists will also be deleted.

So, by using the Restrict delete option we can now achieve true Referential integrity within our list data. Cooll