Referential Integrity between Lists

Problem: SP2010 adds referential integrity between lists using a lookup column.  I wish to push out cascade referential integrity between my lists using declarative CAML to build and link my 2 SharePoint lists.

Initial Hypothesis:  I am going to work through an example to highlight 2 key concepts when deploying a list declaratively using CAML (Using XML which is CAML I am declaratively creating 2 lists namely: Customers and Orders). 
1) I want to display a project field not the ID column that is doing the binding as my lists will make more sense to users updating them.  As shown below:

2)  Setup Cascade referential integrity between the lists (can't be done on the Site Column Lookup).  Referential integrity is done between list instances not on the Site Column.  Therefore, I use a feature receiver to wire up the referential integrity. The result of setting referential integrity is Shown as it will appear in the UI.

There are 3 ways to create lists namely: UI, programmatically or declaratively.  In MOSS the best option for lookup lists was to do it progratically using feature receivers.  My preferred approach is to use the declarative CAML approach.

Tip: The correct way to create a list is to ensure you have followed the 4 steps in this order using CAML: Site Columns, Content Types, List Definition & List Instance.

Tip: CKSDev is an VSIX extension that you can load into Visual Studio 2010 (VS2010) using Microsoft Gallery, it is a codeplex based project that adds functionality to VS2010.  You can use you "Solution Explorer" in VS2010, navigate to the site columns, right click and generate the CAML to declativtively build Content Types and Site columns.  So you can create the content types thru the UI then generate the CAML and now you have a repeatable mechanism of deploying content types and site columns. 

Resolution:
1) Lookup Field (Lookup Site column) should use the customer name not the default ID for linking (This actually links on the customer "ID" but the link displays the "Customers Name".  This is done on the field in the CAML using the attributes "ShowField" and "DisplayName".

2) The second point and title of this posts is about setting referential integrity between lists.  You cannot use the site column declaritive code to create the referential integrity.  The attribute "RelationshipDeleteBehaviour" is used to set the referential integrity behaviour, this can be either None (Default), Restrict or Cascade at a Site Column level.  Using the attribute "RelationshipDeleteBehaviour" does not work as shown below as referential integrity is done between list instances not on the site column.  Why the attribute "RelationshipDeleteBehaviour" is present I have no idea.

Add a new feature to the VS2010 sandbox solution project (Right Click "Features" in "Solution Explorer" and select "Add Feature")

Add the feature Receiver code (Right Click "Feature2" (or whatever you called it), select "Add Event Receiver".

Add the appropriate code to the feature receiver as shown below:

  

Tip:

"You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.

You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model." MSDN

Download:
Download the Visual Studio 2010 project that builds the 2 SharePoint lists in a sandbox solution and adds referential integrity to the list lookup relationship.

More Info:

http://blog.sharepointsite.co.uk/2010/08/creating-lists-programtically-in-sp2010.html

http://msdn.microsoft.com/en-us/library/ff798514.aspx

http://msdn.microsoft.com/en-us/library/ff798409.aspx