CODEWITHSUNDEEP
sqlsql-server
user979331
user979331

Reputation: 11911

SQL - Unique Column 5 random digits

I am fairly new to SQL and I am looking to have a column that will generate 5 unique digits. Much like an id column, except I am looking to get 5 digits that are in random order and be unique for that column

Example:

56829
73591
17469
46290

I do have an id column that is auto increment and unique, but now I need another column that is unique and will generate 5 digits, I have kinda looked into this and apparently there is a uniqueidentifier column type and I have documentation on it, but I am not sure if its what I am looking for....Please help!

Upvotes: 0

Views: 1686

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Perhaps a pseudo-random number would be enough. You could do something like:

alter table t
    add id5 as ( (id * 19379 + 62327) % 99991)

The numbers are just arbitrary prime numbers less than 100,000. Because they are primes, the numbers should not repeat for the values between 1 and 99991.

EDIT:

This is fairly easy to prove for prime numbers. Assume that id1 and id2 have the same value:

(id1 * 19379 + 62327) % 99991) = n
(id2 * 19379 + 62327) % 99991) = n

Then subtract them:

(id1 - id2) * 19379 % 99991 = 0

Admittedly, you need a bit of Number Theory at this point. But, suffice it to say that two primes (19,379 and 99,991) have no divisors in common. So, the only way this can be true is when id1 - id2 is a multiple of 99,991.

(Technically, what you can do is divide by 19,379 because its inverse is well-defined. Inverses are not always well-defined in modulo arithmetic, but when they are division is allowed.)

That latter condition is a fancy way of saying that the numbers do not repeat.

EDIT:

If you want 5 digits, then you can use % 89989 and add 10,000 to the result.

Upvotes: 5

justiceorjustus
justiceorjustus

Reputation: 1965

I've never really used UNIQUEIDENTIFIER in this way, so I may get corrected. This way you should be able to create a unique GUID as the auto-generating unique Id for the row.

CREATE TABLE MyTable (
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    ,UniqueId UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID()
    ,...Other columns
);

Upvotes: 1

Related Questions