Create a unique identifier (PIN) for each table entry

Question

I want to create a PIN that is unique in the table but not incremental to make it more difficult for people. Ideally, I would like to create this in SQL Server, but I can do it through ASP.Net if necessary.

EDIT

Sorry if I wasn’t clear: I’m not looking for a GUID, since all I need is a unique identifier for this table; I just don't want it to be incremental.

Answer 1

Add a uniqueidentifier column to the table with the default value NEWID (). This ensures that each column receives a new unique identifier that is not incremental.

CREATE TABLE MyTable ( ... PIN uniqueidentifier NOT NULL DEFAULT newid() ... ) 

The unique identifier is guaranteed to be unique not only for this table, but for all tables.

If it is too large for your application, you can get a smaller PIN from this number, you can do it as follows:

  SELECT RIGHT(REPLACE((SELECT PIN from MyTable WHERE UserID=...), '-', ''), 4/*PinLength*/) 

Please note that the returned smaller PIN is not guaranteed to be unique to all users, but may be more manageable depending on your application.

EDIT: if you need a small PIN code with guaranteed uniqueness, the difficult part is that you need to know at least the maximum number of users to select the appropriate pin size. As the number of users increases, the likelihood of an IDU collision increases. This is similar to the Coupon Collector problem and is suitable for n log n complexity, which will lead to very slow inserts (the insertion time is proportional to the number of existing elements, so inserting M elements then becomes O (N ^ 2)). The easiest way to avoid this is to use a large unique identifier and select only part of it for your PIN code, assuming that you can refuse the uniqueness of PIN values.

EDIT2:

If you have a table definition like this

 CREATE TABLE YourTable ( [id] [int] IDENTITY(1,1) NOT NULL, [pin] AS (CONVERT(varchar(9),id,0)+RIGHT(pinseed,3)) PERSISTED, [pinseed] [uniqueidentifier] NOT NULL ) 

This will create a pin from the pinseed unique identifier and line identifier. (RAND does not work - since the SQL server will use the same value to initialize multiple rows, this does not apply to NEWID ())

Just to be said, I advise you not to consider it in any way safe. You should keep in mind that it is always possible that another user can guess someone else's PIN code if you do not limit the number of guesses allowed in any way (for example, stop accepting requests after 3 attempts, similar to the bank that placed your card after 3 incorrect PINs -records.)

Answer 2

What you want is a GUID

http://en.wikipedia.org/wiki/Globally_unique_identifier

Most languages ​​have some kind of API to create this ... google search will help;)

Answer 3

What about a column of type UNIQUEIDENTIFIER with the default value NEWID ()?

This will create a new GUID for each row.

Answer 4

Please keep in mind that by requiring a unique PIN code (which is unusual), you will limit the maximum number of authorized users to the PIN specification. Do you really want this?

The solution is not very elegant, but it works - use the UNIQUE field, and then try to insert an arbitrary generated PIN code until the insert is successful.

Answer 5

You can use the following to generate a BIGINT or other data type.

 SELECT CAST(ABS(CHECKSUM(NEWID()))%2000000000+1 as BIGINT) as [PIN] 

This creates a number from 1 to 2 billion. You will simulate some level of randomness, since it is derived from the NEWID function. You can also format the result as you wish.

This does not guarantee uniqueness. I suggest you use a unique restriction on the PIN column. And your code that creates the new PIN should verify that the new value is unique before it assigns the value.

Answer 6

Use a random number.

SET @uid = ROUND (RAND () * 100000)

The less often your values ​​in the table, the better it works. If the number of assigned values ​​becomes large, this is the ratio of the number of available values, it also does not work.

After creating the number, you have several options. 1) INSERT the value inside the repeat loop. If you get a cheat error, restore the value (or try the value +/- 1) and try again. 2) Generate a value and find the existing identifiers MAX and MIN.

 DECLARE @uid INTEGER SET @uid = ROUND(RAND() * 10000, 1) SELECT @uid SELECT MAX(uid) FROM table1 WHERE uid < @uid SELECT MIN(uid) FROM table1 WHERE uid > @uid 

The MIN and MAX values ​​give you a range of available values ​​for operation if a random value is already assigned.