sql server - Trailing spaces allowed in foreign keys -

issue: sql server allows trailing spaces added foreign key!

this behaviour of course leads various unwanted behaviour in application. how can stopped?

example: 2 tables in 1:n relationship:

create table products  (     pid nvarchar(20) primary key ;)  create table sales  (     pid nvarchar(20) references products(pid),      units int ); 

now insert primary key 'a':

insert products (pid) values ('a'); 

now insert foreign keys:

-- 'a' accepted, expected: insert sales (pid, units) values ('a', 23);   -- 'b' declined, expected: insert sales (pid, units) values ('b', 12);   -- 'a ' (with trailing space) -- accepted, of course not expected !!  insert sales (pid, units) values ('a ', 12); 

a second issue hard detect since :

    select pid sales group pid 

returns 1 value: in example

here trick detect issue:

    select pid sales group binary(pid) 

this returns 2 rows: , (with trailing space)

cheers,