A fairly routine task today became a bit of an ordeal, made worse by remembering the symptoms but not the solution. So, I’m documenting this to try and prevent future occurrences.
All I wanted to do was set up a Foreign Key / Primary Key relationship. This should have been relatively straight-forward but wasn’t. The error message from Enterprise Manager told me that it couldn’t create the relationship but didn’t tell why.
Now, I remembered this problem happening before but for the life of me could not remember reasons for it happening. Eventually after getting other people’s views I found the reason, which is that the two columns didn’t have a relationship. I was making too many assumptions.
In future the best way to prevent this error are to follow these two steps.
1.) Make sure column names are fully descriptive. This way the links between should be obvious. See Step 2.
2.) Try and do a join between the two tables. The error I was getting was me trying to link two columns that could not be linked. It might be useful if SQL could tell me that, but it didn’t and perhaps can’t. The confusion arose from there being two IDs, and I was referring to the wrong one. Now, there should only ever be one ID but at the moment with some of my database tables, one ID points to a SharePoint List Item, and the other to a row in the database. The work I’m doing at the moment is to try and get rid of the links between SharePoint and the database so normality can resume.