0

I have a requirement to generate a semi-random code in C#/ASP.NET that has to be unique in the SQL Server database.

These codes need to be generated in batches of up to 100 codes per run.

Given the requirements, I'm not sure how I can do this without generating a code and then checking the database to see if it exists, which seems like a horrible way of doing it.

Here are the requirements:

  • Maximum 10 characters long (alpha-numeric only)
  • Must not be case sensitive
  • User can specify an optional 3 character prefix for the code
  • Must not violate 2 column unique constraint in the database, i.e. must be a unique "code text" within the "category" (CONSTRAINT ucCodes UNIQUE (ColumnCodeText, ColumnCategoryId))

So, given the 10 character limit, GUIDs are not an option. Given the case insensitivity requirement, the mathematical probability for database collisions are fairly high, I think.

At the same time, there are enough possible combinations that a straight look-up table in the DB would be prohibitive, I believe.

Is there a reasonably performant way of generating codes with these requirements that doesn't involve saving them to the DB one code at a time and waiting for a unique key violation to see if it goes through?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1142433
  • 1,413
  • 3
  • 17
  • 34
  • [Is this useful?](https://stackoverflow.com/a/20674397/106159) – Matthew Watson Jul 12 '18 at 17:27
  • What is the business purpose of such a strange code requirement? Seems that among other things it is violating 1NF by sticking an optional prefix onto the value. Then comes all the crazy stuff associated with "random" character strings. And with a length of 10 there is a crazy amount of work that you haven't mentioned to avoid words like sh1th0use and other forms that are all characters. SO MANY problems with code generations like this. – Sean Lange Jul 12 '18 at 18:18
  • Yeah, probably all fair points, but they want what they want. Having already offered multiple alternative and more sane solutions and short of saying "I'm not doing that, because it has 200 potential problems", I need to find a way to satisfy the requirement. To answer your question more directly, the business requirement is "We know our customers can only handle typing and tracking codes in this particular shape, so that's what they need to look like". – user1142433 Jul 12 '18 at 18:22

1 Answers1

0

You have two options here.

  1. You generate a new ID and insert it. If it throws dup unique key exception then try again until you succeed or bail if you run out of IDs. The performance will stink if most of the IDs are used up.
  2. You pregenerate all the possible IDs and store them in a table. Whenever you need to get one you can remove one from a random row index and use that as the ID. Database will take care of the concurrency for you so its guarantee unique. if the first three letters are given then you can simply add a where clause to restrict the rows to match that constraint.
Steve
  • 11,696
  • 7
  • 43
  • 81