identity.md

Serial vs Identity vs UUID

• TLDR, stick to uuid to avoid issues
• use identity only for reference tables, e.g. category, roles, permissions. They usually have a limited number of rows, and they don't expose vulnerability when the ids are exposed to public
• some pitfals when using int id, the foreign key/primary key may clash, resulting in wrong references, e.g. user_id can be passed in admin_id due to confusion, but with uuid this will almost never happen

How to reset sequence?

ALTER SEQUENCE your_sequence_name RESTART WITH 1;

-- To find your sequence name
SELECT * FROM information_schema.sequences;

Sortable uuid

Rearranging v1 uuid in mysql to make it sortable by time https://mysqlserverteam.com/mysql-8-0-uuid-support/ uuidjs/uuid#75

v6 uuid http://gh.peabody.io/uuidv6/

Human readable uuid, base32

https://github.com/solsson/uuid-base32 https://connect2id.com/blog/how-to-generate-human-friendly-identifiers

Postgres Identity

• Don't use serial, use identity
• identity comes in two flavour - generated by default or generated always

drop table users;
create table if not exists users (
	id int generated by default as identity primary key,
--	id int generated always as identity primary key,
--ERROR:  cannot insert a non-DEFAULT value into column "id"
--DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
--HINT:  Use OVERRIDING SYSTEM VALUE to override.
	name text not null
);

insert into users(id, name) values (1, 'jane');
insert into users(name) values ('haha');
alter table users alter column id restart with 10;
table users;