Adding a unique_together constraint to a database model in Django is easy. Add it to the Meta class on your model, makemigrations, migrate, and voilà. This works well if you’re creating a new table, or if you have a low traffic application/low-volume table that won’t take long to migrate. However, in order to add a uniqueness constraint, an index must first be created (so that lookups to confirm if a duplicate already exists can be done quickly). This can be problematic. According to the Postgres docs:
…a standard index build locks out writes (but not reads) on the table until it’s done
This means that while your migration is running, no writes can be made to the table on which the constraint is being added, which, depending on the size of the table, can take some time. In order to avoid locking the table to ensure that user actions can continue to take place during the migration, we’ll have to create the index concurrently, which means we’ll have to do it with raw SQL, instead of using Django’s built-in migrations.
I’d still recommend adding the uniqueness constraint to the Meta class on your model and generating the migration file. You can then run the migration file by running python manage.py migrate <app> <migration_file> --fake. This doesn’t actually run the migration, but it does add it to your django_migrations table, which ensures that once you’re finished, your model definition will match what is in the database, and the next time you try to generate or run migrations, nothing related to this change will happen.
We’ll add the uniqueness constraint concurrently via SQL in two steps:
Let’s jump into it! To add the index to your table, run:
CREATE UNIQUE INDEX CONCURRENTLY <constraint_name> ON <table_name> USING btree (<column_one>, <column_two>);
The key word there is CONCURRENTLY, which means:
PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table
(You can read up on other things you can do when creating indexes in Postgres here, or learn more specifics about building them concurrently here.)
It’s worth noting that if your database contains any records that violate the constraint you’re trying to create when you run this command, it will fail, but will still create an invalid index on your table — you can see this by running \d <table_name> from your database shell. You can remove the index by running DROP INDEX <constraint_name>;, remove the records that violate the index, and then try again.
At this point, with the index created, you can add the actual constraint:
ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name> UNIQUE USING INDEX <name_of_index_created_above>
If you were to inspect your database table via the shell after running this command, you would see a uniqueness constraint that looks just like any other that was created via Django migrations. The only potential exception here is that the naming scheme may look slightly different, since you named the constraint manually via your SQL commands — this has no impact on functionality (just make sure you choose something that tells you what it is!)
Happy migrating!