In SharePoint 2007 we had the ability to add lookup columns.  This was great and a huge step, but it did not allow us to have inherent relationships between lists.  It was quite common (too common unfortunately) that a list entry was deleted and corresponding lists (via a lookup) no longer contained valid information.

This lead to:

• Lists that had no ability to re-select an existing option
• Missing data in parent lists that effected child lists
• No way to know that a parent list had reliant child lists

The Setup

With SharePoint 2010, things change…  Now when we create a new lookup column we get more options:

The important one is right at the bottom:

Deletions

This allows us to force the parent and child lists to have a strong relationship.  Therefore if I delete a record from the parent list…

This is what I will see:

When you have more than one list as a child… unfortunately I don’t get more info.  You only get notified of the first list that is effected by the relationship.

Cascade Updates

Like a good DBA should know… you can also cascade the changes.  So when we change the parent list…

The child list automatically changes too:

Of course you can also cascade DELETE

And again… we get warned:

Interestingly, when we have more than one child with ‘Cascade Delete’ on we get notified of it too:

And if we have a second list (with Restrict Delete turned ON) we still can’t delete it:

This effectively rolls back the whole thing and the lists with ‘Cascade Delete’ are not deleted either.

The Recycle Bin

Now… what about the recycle bin??  Well that is covered too, notice the relational icon:

So if we restore the deleted item… you got it!!! They come back too!!!

So in summary… this is a great step forward for building (more) relational lists is SharePoint 2010!!