Exercise 2: Implement Referential Integrity between SharePoint Lists

In this exercise you will add referential integrity between lists that you will create in this exercise. Referential integrity is a new capability added in SharePoint Foundation 2010.

Task 1 – Enforce Referential Integrity Between Two Lists

1. Open Internet Explorer and navigate to the SharePoint Online site you created for this lab, e.g., https://contoso.sharepoint.com/Lab02.
2. Create a new list named Companies by selecting Site Actions >> More Options…, and then select the Custom List template.

3. Name the list Companies and click Create.

   The Companies list will serve as the parent list in the join between the two lists.

4. Edit the list to add columns by clicking the List tab in the ribbon, selecting Settings and then List Settings:

5. In the Columns section, click the Createcolumn link to create the two additional columns using the values below (leaving all other fields at their default values):
   1. Create a column named Stock Ticker based on column type of Single line of text.
   2. Create a column named Headquarters based on column type of Single line of text.
6. Go back to the list by clicking Companies in the breadcrumb at the top of the page and add the following three list items:

   Title	Stock Ticker	Headquarters
   Microsoft	MSFT	Redmond, WA
   Adventure Works Travel	AWT	London, United Kingdom
   Contoso, Inc.	CONT	Seattle, WA

7. Navigate back to the main page of the Lab02 site.
8. Now you need to create a new list named SKUs that will contain products and be treated as the child in the relationship.
9. Refer to the previous instructions to create a new list SKUs (using the Custom List template).
10. In the SKUs List, go into the List Settings page, and add a lookup column that will act as the join column and enforce referential integrity between Companies and SKUs.
    1. Name the new column Manufacturer based on a column type of Lookup.
    2. In the Get information from drop down, choose Companies.

    3. In the Add a column to show each of these additional fields section, select the Headquarters field.

       This setting will cause SharePoint to project the Companies.Headquarters field into the views within the SKUs list containing the value corresponding to the list item selected in the Manufacturer field. This is yet another new and exciting ability in SharePoint 2010 that is new in SharePoint Online.

    4. Check the Enforce relationship behavior checkbox and select Restrict delete.
       Note:

       Note that these last two selections will prohibit users from deleting items in the Companies list if that item is referenced by items in the SKUs list.
    5. Click OK.
    6. When prompted, accept the dialog prompting you to create an index on this column. Linked columns in joins must be indexed.
       Note:

       Error: Note format was corrupted. Title should be bold.
11. Create two additional columns for this list.
    1. Create a column named Description based on a column type of Single line of text.
    2. Create a column named Cost based on a column type of Currency.
12. Navigate back to the SKU’s list and add the following items to that list using the following information.

    Title	Manufacturer	Description	Cost
    SharePoint Foundation 2010	Microsoft	SharePoint Foundation 2010	$0.00
    Office	Microsoft	Office 2010 Client Applications	$100.00
    Destination Guide Seattle	Adventure Works Travel	Travel guide for Seattle, Washington	$25.00
    Destination Guide St. Johns	Adventure Works Travel	Travel guide for St. Johns, FL	$25.00
    Do-it-Yourself Advanced Fireworks Set	Contoso, Inc.	Create your own fireworks indoor with this advanced fireworks set. Real gunpowder! Age 4-7.	$15.00
    Model Flying Fortress Airplane	Contoso, Inc.	Build a replica of a historic American bomber	$10.00

13. When finished, you should see a list of products. Notice how fields from the Companies list were pulled down into this list.

Task 2 – Test Referential Integrity Constraints

In this task, you will test the Restrict Delete and Cascade Delete referential integrity constraint between the Companies and SKUs lists.

1. Test the referential integrity between the Companies and SKUs lists. Try and delete the Adventure Works Travel item form the Companies list. You will be sent to an error page.

2. To test out the cascading delete capability, change the referential integrity constraint behavior on the Manufacturer column in the SKUs list to Cascade delete.

3. Try again to delete Adventure Works Travel from the Companies list.

   Be sure to read the warning message that informs you exactly what will happen if you click OK. Now click OK. If you browse to the SKUs list, you’ll see two items are now gone.

4. When the parent and child items were deleted, they were all deleted in a single atomic unit. Look inside the Recycle Bin (available via the Quick Launch menu on the left side of the screen). You’ll notice a special icon indicating there are multiple items in this deletion set.