Consider two tables: teams and players. The id column of teams is a UUID (auto generated via uuid_generate_v4()) andit is referenced by players.team_id.

For reasons, the primary key of teams needs to change from UUID to a shorter random string format.

Once again, not SQL wizardry but this solution maybe not so obvious to others.

There are two parts to this problem: auto generating an 8 character random string as primary key for a table and how to update the database gracefully, considering the table has references in other tables.

Random string as default

Let me start by clearly saying: this process of generating a random string is not the best nor it is safe for cryptographic purposes! It was a more than good enough solution for the problem at hand, in this application/project.

How is this achieved? By generating a (pseudo) random number, generating the MD5 hash of it, and cutting whatever part we want.

SELECT SUBSTRING(MD5(RANDOM()::text), 0, 9);

Graceful schema/data update

This part is slightly more involved as we need to gradually update the schema as we manipulate the existing data to adhere to it. This is required mostly because the table that requires this PK update is already referenced by other tables.

The process is described in comments in the snippet bellow.

This was executed in a migration, so the transaction is implicit. If that is not the case, this obviously should be executed in one.

-- create temporary new id already with desired default
ALTER TABLE teams ADD COLUMN id_v2 text NOT NULL UNIQUE DEFAULT SUBSTRING(MD5(RANDOM()::text), 0, 9);

-- add new "foreign key to be" column
ALTER TABLE players ADD COLUMN team_id_v2 text;

-- update new "fk to be" with new ids generated by the first alter on teams
WITH cte_teams AS (
    SELECT id, id_v2
    FROM teams
)
UPDATE players
SET team_id_v2 = cte_teams.id_v2
FROM cte_teams
WHERE players.id = cte_teams.id;

-- drop the existing foreign key constraint
ALTER TABLE players DROP CONSTRAINT players_team_id_fkey;

-- drop the current foreign key column
ALTER TABLE players DROP COLUMN team_id;

-- rename the "fk to be" to the name of the previous foreign key (just dropped)
ALTER TABLE players RENAME COLUMN team_id_v2 TO team_id;

-- drop main table primary key constraint
ALTER TABLE teams DROP CONSTRAINT teams_pkey;

-- drop main table id column
ALTER TABLE teams DROP COLUMN id;

-- rename temporary id to definitive version
ALTER TABLE teams RENAME COLUMN id_v2 TO id;

-- add back fk constraint
ALTER TABLE players ADD CONSTRAINT players_team_id_fkey FOREIGN KEY (team_id) REFERENCES teams (id);

-- add back pk constraint
ALTER TABLE teams ADD CONSTRAINT teams_pkey PRIMARY KEY (id);