Migrating Foreign Keys in PostgreSQL

Applications often need to work with external IDs, e.g. UUIDs of third party services or SKUs. In such cases, an external ID should be stored just once in a mapping table and from there on only referenced by an internal (e.g. serial) foreign key. Without a dedicated mapping table, you will carry such external identifiers through all tables. This may seem reasonable at the beginning, but may soon cause trouble, e.g. if you want to add a second type of external service or when you realize, that those seemingly unique identifiers are not as unique as assumed.

Even though I should know better, this kind of mistake happens to me from time to time anyway. Or it happens to others and I have to save the day.

Because PostgreSQL is not the wild west, if you reference a field in a different table, this will be enforced through a constraint, which is fantastic for data consistency, but annoying if you misdesigned your data structure and have to change your foreign keys. But, don’t worry, it’s still possible to change this inside a single transaction with those four-ish steps:

0. Create the new key ID column, make it unique (e.g. serial)
1. Identify all tables which reference the (old) ID:
   1.1. Note the table name
   1.2. Find the referencing column
   1.3. Determine the foreign key constraint name (see pg_constraint table)
2. For each referencing table:
   2.1. Drop the existing constraint (from 1.3.)
   2.2. Update the referencing key values to the new ones
   2.3. Add new constraint for the new base table ID
3. Drop primary key constraint from base table
4. Create new primary key constraint on base table

Example

Let’s assume your application tracks users, which have external identifiers. At the moment you got a user table, which has this external ID as the primary key:

user

Thus, all tables that reference any user have those long, external identifiers:

user_permissions

Now, if you want to gain flexbility and add an internal ID, you can start off with adding a serial column to users:

ALTER TABLE user ADD column id serial UNIQUE;

user would now look like this:

Now, for every referencing table, you need to strip the constraint, update the values to the new serial ID and then add the new constraint:

ALTER TABLE user_permissions
	DROP CONSTRAINT user_permissions_user_id_...id;

UPDATE user_permissions
	SET user_id = (
		SELECT id
		FROM user
		WHERE external_user_id = user_permissions.user_id
	);

ALTER TABLE user_permissions
	ADD CONSTRAINT user_permissions_user_id_...id
		FOREIGN KEY (user_id)
			REFERENCES user (id);

user_permissions looks now like this:

Finally, you can now change user.external_user_id without constraints, you just have to swap out the primary key in user:

ALTER TABLE user DROP CONSTRAINT user_pkey;
ALTER TABLE user ADD PRIMARY KEY (id);

At this moment, you can even put the external identifier into a different table, if you wish, thus possibly separating the concerns even better.

Bonus Content for Django Users

Swapping out primary keys is too difficult for the automatic Django migration assistant, so you’ll need to get your hands dirty. Obviously, you can do all those steps as raw SQL migrations, but then makemigrations will still nag you about changes in your model, thus you got to use SeparateDatabaseAndState, apply the changes with SQL and then tell Django what the effect is. For an example, see my code in osmcal.

Extra Bonus

If you’re working with raw SQL migrations, you might run into trouble and all you’ll get is some context-free SQL error message. For better debugging, you can use the sqlmigrate subcommand which will display the SQL queries instead of directly executing them. That way you can step through the statements and debug them step by step.

./manage.py sqlmigrate <app name> <migration name>