Today I Learned

hashrocket A Hashrocket project

Adding Composite Uniqueness Constraints (Postgres)

There are two ways in Postgres to create a composite uniqueness constraint; that is, a constraint that ensures that the combination of two or more values on a table only appear once. For the following two code snippets, assume that we have a table relating Pokemon and Trainers and that our domain restricts each Trainer to only having at most one of each Pokemon.

The first approach is to create a constraint directly on the table:

alter table pokemons_trainers
  add constraint pokemons_trainers_pokemon_id_trainer_id_key
  unique (pokemon_id, trainer_id);

The second approach is to create a unique index:

create unique index pokemons_trainers_pokemon_id_trainer_id_idx
  on pokemons_trainers (pokemon_id, trainer_id);
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.