How do I auto generate a 4 digit pin SQL?-CodePudding

Similar idea to this post: How do I generate a 4-digit pin no duplicating number

However is there a way to do this automatically when a new user is created rather then setting them to random?

I'd like to be able to create a new user in my system and have their pin number automatically randomize a 4 digit number. Is AUTO_INCREMENT the only automatic option?

I have used this:

UPDATE patrons SET pin=FLOOR(1000 (RAND() *8999)) WHERE pin = ?

However it's not what I want as it has to be done manually. I have created a method in my SQL DAO class in my Java program to do this after a user is created so it does work, but if I was to create a new user in Maria DB it would just do the auto increment by one value each time. I would prefer if the random generation would happen whichever way I use my database.

I'd greatly appreciate some help!

Update

I do not need the pin to be unique as the user will also need their library card number as well in order to log into the system.

CodePudding user response：

4-digit PINs are not supposed to be unique, because users should be able change their PINs when they want, to whatever they want. Moreover, when there are more than 10'000 users, they cannot be unique (by pigeonhole principle).

Thus, this code:

UPDATE patrons SET pin=FLOOR(1000   (RAND() *8999)) WHERE pin = ?

is wrong, because it will change the PINs of all users that share the same PIN, when only one user decides to change the PIN. The PIN should be changed for the user, and not for the number.

I would say: random generation of default PIN, with some provided entropy that would make the next PIN hard to guess, is the best. Auto-increment is bad for security reasons, and the standard random generators are bad for the same reason: the next PIN value is only slightly harder to guess than the increment. This should be different than changing PIN by user request — in this case the number provided by the user should be used.

PostgreSQL, for example, has gen_random_bytes for cryptographic-strength random generation. I suggest looking for something similar on your platform of choice.

CodePudding user response：

Create a trigger the fires on insert:

create trigger client_pin
before insert on client
for each row set new.pin = 1000   rand() * 9000;

See live demo of below:

create table client (
  id int auto_increment primary key,
  name text not null,
  pin int(4)
);

create trigger client_pin
before insert on client
for each row set new.pin = 1000   rand() * 9000;

insert into client (name) values ('Adam');
insert into client (name) values ('Betty');

It would be better if pin was calculated using rand() * 10000 and you handled leading zeros with formatting, for example by selecting

lpad(pin, 4, '0') as pin

instead of selecting pin directly.