Use of capitals in relationships

Discussion created by andypieman on Apr 4, 2017

As I was writing this and trying to come up with a demonstration file I answered my own question. I'm therefore putting this up as information just in case someone else has the same problem, and for other useful comments, though please no lectures on best practices for primary keys. This is what I had to work with.



I have picked up a DB that uses user entered primary keys (yes I know!). There is a strict formula for these, and on the whole everything is ok. This is except where the codes have been entered with an occasional capital letter. In a table the matching looks fine, the code with capitals and the matching without problem.


The problem arises when I try to do a find on the related table. The image below shows the result of looking for blanks on the related field ::ActivityCode (Omit *). It brings up blanks, and any instances where the primary keys (::ActivityCode and Aktivtetskode) have capitalisation issues.


Any other activities on this match seems to run ok, though I am a little worried this could also have knock-on-effects of storage behaviour because I don't understand the logic behind this.

Untitled 4.jpg


The solution to this was that the key fields were indexed differently. One side was Unicode, hence the mismatch with capitals, the other side was Danish, hence the match!