Best practices on primary key, auto-increment, and UUID in SQL databases [closed]

It’s a matter of choice actually and this question can raise opinion based answers from my point of view. What I always do, even if it’s redundant is I create primary key on auto increment column (I call it technical key) to keep it consistent within the database, allow for “primary key” to change in case something went wrong at design phase and also allow for less space to be consumed in case that key is being pointed to by foreign key constraint in any other table and also I make the candidate key unique and not null.

Technical key is something you don’t normally show to end users, unless you decide to. This can be the same for other technical columns that you’re keeping only at database level for any purpose you may need like modify date, create date, version, user who changed the record and more.

In this case I would go for your second option, but slightly modified:

CREATE TABLE users(
  pk INT NOT NULL AUTO_INCREMENT,
  id UUID NOT NULL,
  .....
  PRIMARY KEY(pk),
  UNIQUE(id)
);