Lookup fields are the equivalent of foreign keys if you consider SharePoint lists as tables of a relational data base. When creating a lookup site column, you just need to choose which list and field (of that list) will be the target of the lookup. However, this kind of fields has a few limitations:
- The target list must reside in the same web site as the field you are defining;
- You cannot, using SharePoint's UI, change the target list/field after the lookup field is created;
- After the lookup field is created, SharePoint will still allow the target list and target field to be deleted, which might lead to data inconsistencies.
To make this problem easier to understand, consider the following example:
- In Site A there's a list called Cities;
- You create a lookup site column, named City, whose target is the Title field of the Cities list;
- You create a site content type, named Address, that includes the City lookup field;
- If you delete the Cities list, no error is thrown by SharePoint;
- Even if you create a new list called Cities, it will have a different ID and, for that reason, it will not be recongnized as the target of the lookup;
- If you try changing the City field, you won't be able to change the target list or target field.
The only solution is to remove the field from the content type, delete it and start everything for scratch… but when you do so, you lose all the information the column contained in each of the lists where you used it.
