Add and Remove Unique Constraints in PostgreSQL

Constraints are a great way to ensure that an app's data conforms to certain business rules.

For instance, many apps have user accounts that are identified by an email address. If a person's email address is how you uniquely identify them, then a business rule would be, "no two user accounts have the same email address." Enforcing these rules at the database layer gives you some extra assurance that fishy data won't be able to wiggle its way in.

Here is one way of adding a unique constraint to our users table for the email column:

alter table users add unique (email);

This will add a constraint with the following description:

"users_email_key" UNIQUE CONSTRAINT, btree (email)

The name of the constraint was generated by Postgres. It appended <table name>_<column name>_key together. We can specify our own custom constraint name if we'd like though.

alter table users add constraint user_emails_are_unique unique (email);

This will create a similar constraint with our preferred name:

"user_emails_are_unique" UNIQUE CONSTRAINT, btree (email)

If at any point you or your application try to insert a record into the users table with an existing email address, you'll see something like the following:

ERROR:  duplicate key value violates unique constraint "user_emails_are_unique"

If you're writing a migration (e.g. ActiveRecord migrations), then you'll want to write the reverse action in the event you need to rollback. In our case, we need to drop the constraint. Because we specified our own name for the constraint, it'll be easy to drop it by name.

alter table users drop constraint user_emails_are_unique;

Check out the PostgreSQL docs for more on constraints.