primary key - SQL Server "pseudo/synthetic" composite Id(key)

Question

Sorry but I don't know how to call in the Title what I need.

I want to create an unique key where each two digits of the number identify other table PK. Lets say I have below Pks in this 3 tables:

Id  Company     Id  Area        Id  Role
1   Abc         1   HR          1   Assistant
2   Xyz         2   Financial   2   Manager
3   Qwe         3   Sales       3   VP

Now I need to insert values in other table, I know that I may do in 3 columns and create a Composite Key to reach integrity and uniqueness as below:

Id_Company  Id_Area Id_Role ...Other_Columns.....
1           2       1
1           1       2
2           2       2
3           3       3

But I was thinking in create a single column where each X digites identify each FK. So the above table 3 first columns become like below (suposing each digit in an FK)

Id  ...Other_Columns.....
121
112
222
333

I don't know how to call it and even if it's stupid but it makes sense for me, where I can select for a single column and in case of need some join I just need to split number each X digits by my definition.

Answer 1

It's called a "smart", "intelligent" or "concatenated" key. It's a bad idea. It is fragile, leads to update problems and impedes the DBMS. The DBMS and query language are designed for you to describe your application via base tables in a straightforward way. Use them as they were intended.