SQL Server CE nvarchar foreign keys with trailing whitespace

In SQL Server CE, foreign key constraints on nvarchar fields are only enforced after dropping the trailing whitespace. This means that if the PK is "foo " I can insert "foo" into the FK.

Why is this the case? It seems to badly undermine the data integrity the foreign key system is supposed to prov开发者_JS百科ide.

Is there any way to enforce a foreign key constraint such that whitespace is included in the comparison? What options do I have for working around this behavior?

Replacing the FK fields with ints is the most obvious solution, but is a last resort (in my case) due to the way the related application has been implemented.

That is correct. Your only choice is to not use string based FK's. You can create a lookup table with the strings in them that assigned a uniqueID you then use for your FK's if you like.