Composite Foreign Key in Sharepoint List

Question

I have two sharepoint List.

List1 - This contains all users and has primary key as UserId List2 - This contains all courses and has primary key as CourseId

Now I want to define third list that would have its primary key as composite key. List3 - This contains users to courses mapping. So the primary key is (UserId, CourseId) combination.

When I define List3, I add columns from the List Settings page. When I create a new column, I can choose Lookup and choose the foreign key from another list. But I am unable to define a composite key. (where the keys come from different list).

Is there a way to define composite key for a list in sharepoint 2010?

Thanks

Answer 1

Sharepoint is not a database and lists are not tables. There are no primary, foreign keys or relations (in the DB sense) in Sharepoint. While the DB metaphor is used to explain Sharepoint it is just an metaphor, not the way Sharepoint works. The closest thing to Sharepoint you can find in the database world are document databases.

Perhaps you are confusing lookup fields with primary keys. A lookup field is a field whose values come from another list. They do not define a relation between the lists. That said, Sharepoint 2010 allows you to enforce deletion constraints so that you can't delete a list item that is used as a lookup value in another list. This is set at the lookup field level and only if the lookup field allows a single choice.

To set the restriction programmatically, use the SPFieldLookup.RelationshipDeleteBehavior property. To set the restriction from the UI, locate the lookup field in the target list's settings, open its settings and enable "Enforce Relationship Behavior" in the Relations section.