Member Avatar for zachattack05

I am considering an option for users that will allow them to write their own account numbers or create their own algorythm for creating custom account numbers for customers, but also want an option that will auto-generate them if the user doesn't care by using the identity property in SQL.

I've always heard that if the PK is supposed to mean anything to users (which an account number would), that using the identity feature is a bad idea, but was never really certain on the reason(s) why?

Any suggestions or thoughts on this?

  • 4 Contributors
  • 4 Replies
  • 187 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by reshea1950

Dani AI

Generated

Short answer: don’t bind your user-visible account number to the IDENTITY value. Use a small, stable surrogate primary key for joins and a separate account_number column for anything customers see or enter — let customers type a number or let the system generate one into that second column. This keeps foreign keys simple, avoids rebuilds later and lets you enforce format/uniqueness separately from the internal row id; it also corrects ’s 1NF comment (having both a surrogate PK and a business key is normal practice). (sqlshack.com)

Example pattern (keeps IDENTITY as the internal PK, account number unique and optional):

CREATE SEQUENCE dbo.AccountSeq AS BIGINT START WITH 100000 INCREMENT BY 1;

CREATE TABLE dbo.Customers (
  CustomerID INT IDENTITY(1,1) PRIMARY KEY,
  AccountNumber VARCHAR(20) NULL CONSTRAINT UQ_Customers_AccountNumber UNIQUE,
  Name NVARCHAR(200) NOT NULL
);

ALTER TABLE dbo.Customers
ADD CONSTRAINT DF_Customers_AccountNumber
DEFAULT ('AC' + RIGHT('000000' + CONVERT(varchar(10), NEXT VALUE FOR dbo.AccountSeq),6))
FOR AccountNumber;

Practical reasons to avoid exposing IDENTITY as the business number: identity values aren’t guaranteed gapless (caching and rollbacks can create jumps) and SQL Server has database-level identity caching behavior you may need to manage; that makes the sequence unsuitable when customers expect tidy, contiguous numbers. You also leak easily-guessable, sequential IDs (enumeration / IDOR risk). If you later decide you need a different format it’s painful to change an IDENTITY column. (learn.microsoft.com)

Options: use a SEQUENCE + default (NEXT VALUE FOR) to centralize generation and add formatting; use GUIDs only when you need global uniqueness; or implement a serialized counter table if you absolutely must have gapless, ordered numbers (note: gapless guarantees serialize work and hurt throughput). For SEQUENCE usage see Microsoft docs. (learn.microsoft.com)

Checklist: keep the IDENTITY as an opaque PK, enforce UNIQUE/INDEX on account_number, validate/format numbers in a single place (DB constraint or service), avoid exposing internal PKs in public URLs, and confirm any business-format requirements up front so the user-facing column can be designed to match them. (sqlshack.com)

Recommended Answers

All 4 Replies

Member Avatar for Momerath

I've never heard that, whatever that's worth :)

You'll be violating first normal form if you provide both an identity key and an account key, but sometimes that's just what you have to do. I'd put a trigger on the insert and set account = id if account is null.

Member Avatar for zachattack05

Well what I intended to do was during the setup of the application, ask "do you want to provide account numbers, or auto-generate them?" and if they want to auto-generate them the database would be built setting the identity property to true, and if they want to make up their own, set it to false and let them type their own account number.

I would only have one column, just depends if the identity property is set to true or false when the database is built.

Edited by zachattack05 because: correction
Member Avatar for rotten69

I'd make the account number as a primary key/unique in the database so that way you can pretty much avoid any misleading voilating numbers given by customers because they don't really know what numbers are stored.

Member Avatar for reshea1950

Sometimes for business requirements reasons customer numbers need to be in a particular format or even numbering sequence.
For generating a unique customer number, it sounds that using the Identity number would be OK. At least at first blush.
I am sure that you checked with the business users to see if the number sequence for customers that will be generated will be OK.
I only mention this non-technical issue because we had different business units that demanded a different customer number sequence scheme for each business unit.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.