tsql - Is it possible to create a Foreign Key on 2 columns with differing Collations? -

i've tried searching hour through foreign key / collation questions can't find remotely close question.

i have 2 tables 2 different software vendors sitting in same database. 1 vendor hard codes collations latin1_general_bin while other uses database default (in case latin1_general_ci_as). possible, without altering of columns, create foreign keys between these 2 collation types?

typically change 1 in case tables sensitive , i'm not allowed make such change have create foreign key due piece of logic in trigger reads data between these 2 tables if finds foreign key:

select column_name information_schema.constraint_column_usage  constraint_name =  (      select name sys.foreign_keys      parent_object_id = object_id( 'table1' )     , referenced_object_id = object_id( 'table2' )  ) 

any appreciate

p.s. can't seem figure out how code thing works if me out, put in 4 required spaces it's still displaying code text :(

adding foreign key constraint field of 1 collation field of collation can't done. error message 1757.

either change collation of 1 of tables or create work around new column used instead correct collation or create surrogate key columns integers used referencing.

if nothing else works , really need fix type of constraint , performance not issue, add check constraints and/or triggers check referential integrity of data put tables. these rules have cast values in 1 table collation of other in order compare values slow , tricky use of indexes, proceed caution.

for example have insert trigger on referencing table check if record inserted string exists in referenced table. have add update , delete trigger referenced table doesn't fall out of range of values referenced records in referencing table or cascades updates/deletes. replicate foreign keys , gets slow , scales horribly.

short answer: don't it, let tables stay untied or fix collation of 1 of them.