SQL Server“伪/合成"复合 ID(键) [英] SQL Server "pseudo/synthetic" composite Id(key)

问题描述

抱歉，我不知道如何在标题中调用我需要的内容.

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

我想创建一个唯一的密钥，其中数字的每两位数字标识其他表 PK.假设我在这 3 个表中有以下 Pks:

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

现在我需要在其他表中插入值，我知道我可以在 3 列中插入并创建一个复合键以达到完整性和唯一性，如下所示:

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

但我想创建一个单独的列，其中每个 X 数字标识每个 FK.所以上表3第一列变成如下(假设每个数字在一个FK中)

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

我不知道如何称呼它，即使它很愚蠢，但对我来说很有意义，我可以在其中选择单个列，如果需要加入，我只需要按我的每个 X 位数拆分数字定义.

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.

推荐答案

它被称为智能"、智能"或串联"" 键.这是个坏主意.它很脆弱，会导致更新问题并阻碍 DBMS.DBMS 和查询语言旨在让您通过基表以一种直接的方式描述您的应用程序.按预期使用它们.

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.

这篇关于SQL Server“伪/合成"复合 ID(键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！