具有自动增量的自定义主键生成 [英] Custom PrimaryKey Generation with autoincrement

问题描述

我需要为2个或更多表定义并生成主键.

I need to define and generate primary key for 2 or more tables.

表包含相同类型的数据，但是对于某些业务规则，我们必须将它们分开说

Tables hold same type of data but FOR Some BUSINESS RULES we have to make them separate say like

TableA = LOCAL_CUSTOMERS
表格B = INTERNATIONAL_CUSTOMERS

TableA = LOCAL_CUSTOMERS
TableB = INTERNATIONAL_CUSTOMERS

两者都包含相同的列，例如CUSTOMER_ID C_NAME，CITY .... etc

Both hold same columns like, CUSTOMER_ID C_NAME, CITY ....etc

在设计主键时，我希望这些键是以下各项的组合 PREFIX和一个自动生成的数字，就像一个自动隐蔽的int PK一样.

While designing the Primary Key I want these keys to be a combination of PREFIX and an auto generated number, as an auto increamented int PK will do.

例如，表"LOCAL_CUSTOMERS"的CUSTOMER_ID(PK)

for example, CUSTOMER_ID (PK) for the table "LOCAL_CUSTOMERS"

LOC1，LOC2，LOC3，... LOC5000

LOC1, LOC2, LOC3,...... LOC5000

和"INTERNATIONAL_CUSTOMERS"表的CUSTOMER_ID(PK)

and CUSTOMER_ID (PK) for the table "INTERNATIONAL_CUSTOMERS"

INT1，INT2，INT3，... INT5000

INT1, INT2, INT3,...... INT5000

WHERE LOC和INT是LOCAL，INTERNATIONAL的前缀.

WHERE LOC and INT are prefix for LOCAL , INTERNATIONAL.

我们可以使用int在身份规范"中设置自动递增，但是我该怎么做呢? 我需要为列设置计算列规范"吗?

we can set auto increment in "identity specification" by using int but how can i do this ? Is it "Computed Column Specification" I need to set for the column ?

谢谢

推荐答案

您绝对可以使用计算列来做到这一点.

You can definitely do this with a computed column.

或者，如果您坚持使用两个表，则可以只添加一个计算的，持久化的列，如下所示:

Either, if you stick with your two tables, you could just simply add a single computed, persisted column like this:

ALTER TABLE Local_Customer
  ADD CustomerID AS 'LOC' + CAST(ID AS VARCHAR(7)) PERSISTED

ALTER TABLE International_Customer
  ADD CustomerID AS 'INT' + CAST(ID AS VARCHAR(7)) PERSISTED

如果您决定有一个带有歧视者列的表格，例如BIT类型的"IsDomestic"，您可以根据"IsDomestic"列的值来做一个将使用"LOC"或"INT"作为前缀的计算列:

If you decide to have a single table with a discrimator column, e.g. "IsDomestic" of type BIT, you could do a single computed column that will use "LOC" or "INT" as prefix, based on the value of the "IsDomestic" column:

ALTER TABLE Customer
  ADD CustomerID AS CASE IsDomestic WHEN 0 THEN 'INT' ELSE 'LOC' END + CAST(ID AS VARCHAR(7)) PERSISTED

无论哪种方式-您的INT IDENTITY类型的"ID"字段都会为每行自动增加，并且计算出的列将自动创建更易于理解的"CustomerID"，而无需您付出任何额外的努力.

Either way - your "ID" field of type INT IDENTITY will be automatically increased for each row, and the computed column will create a more human-readable "CustomerID" automagically, without any further effort on your part.

这篇关于具有自动增量的自定义主键生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！