SharePoint Relational Lists

One of the things I was most excited to see in SharePoint 2010 was the introduction of true relational lists. (Am I betraying my database background here or what?) 

Relational Lists seems like a no-brainer, since SharePoint's data is resident in a SQL relational database, right? Well, true though that is, the data for individual lists is NOT maintained in distinct tables. Instead they are grouped together into an "AllLists" table or an "AllLibraries" table. Weird huh?
The lookup field is not new to SharePoint 2010, but it now has capabilities to enforce relationship behavior between two lists that share common values.  That means we can build joins between lists, we can use "Projected Fields" (fields from the parent table can be shown read-only in a view of a child table), and we can enforce relational integrity between our tables (you can't delete a parent row if it is used by a child row) .
Sounds great, right? So here's how you go about the business of building relationships between SharePoint lists.
1. Create a list to hold "parent" records.
In my example this will be a "Contacts" list.
2. Open the List Settings.
In the list of columns, ensure that whichever column you want to use for selecting records in the child table is required and unique. This is not required, but will help to ensure that record selection is done correctly later.


2. Add data to this "parent" list.

3. Create a "child" list.
In my example I will create a new Custom List called "Phone Conversations" that will be used for documenting phone calls with contacts. In my example I will
 a. Rename "title" to "subject"
 b. Create a new date/time field to store the Call Date
 c. Create a new multiple-line text field to store Call Notes
 d. Create a new LOOKUP field called Contact. Here are the properties as I set them to support a Relational Lookup:


The "In this column" field determines what the person filling out the form is going to be able to see when selecting a contact from the contacts list. The checkboxes underneath will define the other columns that can be displayed when creating a view from this list or when viewing the properties of any particular list in this list.
The "Enforce relationship behavior" option defines a rule that can be summed up as this:
"No Orphans allowed!"
This rule is often refered to as a "referential integrity" rule or constraint upon the data. It means that in our example, we are not allowed to delete a contact if that particular contact is in use by a phone conversation record. More on that in a moment.
5. Final Step - add some "child" list data.
Here is a new record being created in the phone conversations list calling for information in the contacts list:


And here is the record after being added:



As you can see, we have several columns of "joined" data. Information that comes from the contacts table can be displayed for related records again and again without any new data entry.
Now about that "Enforce Relationship Behavior" business.  Even with the setting configured for "Restrict Delete" I can delete phone conversations and any unused contacts with impunity. However, this is the dialog box I will see if I try and delete a contact that is in use:





Now if I were to go back to the properties of the Lookup field in the Phone Conversations list called "Contact" and change the referential integrity option to "Cascade Delete" I would not get this message. Instead I WILL be able to delete parent records... by also deleting all the related children! Here is the message you would see if deleting a contact that is attached to a phone conversation:



After clicking OK the parent record deletes as usual, and both the contact and the related phone conversations are moved to the recycle bin. What if you want them back? You restore the Parent from the recycle bin - you won't be able to see the "cascade-deleted" records - but the icons in the recycle bin can help:




And there you have it! Enjoy your truly connected lists with referential integrity within SharePoint. I'm curious - what purposes would you have for a SharePoint relational list? Please comment below!