Custom PrimaryKey Generation with autoincrement

Question

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
TableB = INTERNATIONAL_CUSTOMERS

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

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.

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

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

and CUSTOMER_ID (PK) for the table "INTERNATIONAL_CUSTOMERS"

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

WHERE LOC and INT are prefix for LOCAL , INTERNATIONAL.

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 ?

Thanks

Answer 1

you could have a composite key over the zone id and auto id.

could also have a trigger that sets the pk to a custom string upon insert.

in general, would be best to keep these in the same table (since they have the same columns) and only differ by local vs. intl.

Answer 2

I think you should keep them in the same table if at all possible, and add another column to distinguish between local and international customers. With 2 tables, you double all the work you'll need to do in the future on these tables. Not to mention using a calculated varchar id value instead of a simple generated long id.

Answer 3

What you are trying to implement is referred to as subtypes: special cases of the same kind of object. There are a few common ways to implement it:

1. make one table containing all the columns required for both types. Use NULLable columns for those properties that do not apply to all types, and mandatory columns for all other properties. There is only one table, so only on pk.
2. create one table with the generated pk for all common properties (the base type). Add separate tables for each subtype. these tables have a pk that uses a foreign key to point to the pk of the base type (and thus, have a 1:1 relationship with th base type). So both types draw from the same set of key values

(there are more ways, but these are most useful, and most popular)

I would advise vehementlyu against all kinds of "smart" keys. Of course, you can have a " type" column in your table (the one for the base type) to quickly determine what you are dealing with. But a key scheme like you are suggesting is just complicating things with no real value.

Answer 4

If your proposed 2 tables have the same columns, then I see no reason why you should make them separate tables at all. Create a column for storing the type of customer (international or domestic) and keep track that way. Why use 2 tables when 1 will suffice?

Answer 5

The other answers all make valid points.

I would like to add, since it appears there is some desire for there to be separate sequences for both local and international customers, that relying on any particular sequential behavior is a bad idea. In particular, for identity columns there can be gaps if an insertion fails and is rolled back (an identity is reserved for the transaction immediately, but if a constraint or trigger fails it will never be inserted). It is also possible to change the seed and spacing and also to use identity insert to insert non-sequential values.

I would advocate for a true surrogate identity column with a customer type flag. Your identity column will be unique across all customers. If you absolutely want separate sequences you can either write a trigger to get a true non-gapped sequence or use identity. Then you can use a persisted computed column as you suggest to build the combined key and put an index on that column depending on your needs.

Answer 6

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

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

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.