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.

1. Open a browser and navigate to the site created in the previous exercise.
2. Create a new list named Companies by selecting Site Actions » More Options…, and then select the Custom List template. Name the list Companies.

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

   Figure 1

   Click the List Settings button on the List tab

4. Now create two new columns for this list using the Create column link below the Columns section of the list settings page. Create the two columns using the following values leaving all other fields 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.
5. Go back to the list by clicking Companies in the breadcrumb at the top of the page. Now add three items to the list. This is done using the ribbon again. Select the Items tab, then New Item and finally New Item.

6. Add the following items to this list using the following information.

7. The Companies list will serve as the parent list in the join. Navigate back to the main page of the Lab04 site. Now you need to create a new list named SKUs that will contain products and be treated as the child in the relationship. Refer to the previous instructions to create a new list SKUs (using the Custom List template).
8. In the SKUs List, go into the List Settings page, and add a lookup column that will (1) act as the join column & (2) enforce referential integrity between Companies and SKUs.
   1. Name the new column Manufacturer based on a column type of Lookup.
   2. For the column setting of Get information from assign a value of Companies.
   3. Add a column to show each of these additional fields: Headquarters. Note that 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.
   4. Make sure the column setting Enforce relationship behavior is checked
   5. Make sure the column setting Restrict Delete is checked
   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.
   1. Click OK.
   2. When prompted, accept the dialog prompting you to create an index on this column. Linked columns in joins must be indexed.
   Note:

   If you cancel the dialog, the column will not be created and added to the list.
9. Now, 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.

10. Navigate back to the SKU’s list and add the following items to that list using the following information.

11. When finished, you should see a list of products. However, you should notice that fields from the Companies list were pulled down into this list!

    Figure 2

    The Products list

12. Now, test the referential integrity of the lists. Go try to delete the Adventure Works Travel item form the Companies list. You will be sent to an error page.
13. To test out the cascading delete capability, go change the Manufacturer column in the SKUs list to Cascade Delete. Now, delete the same Adventure Works Travel item from Companies. 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.

14. 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.

    Figure 3

    The deleted item in the Recycle Bin

    Note:

    In this exercise you created two joined lists that utilized the referential integrity constraints provided in SharePoint.