PostgreSQL – How to Add a Unique Constraint
You can add a unique constraint to a table column using the ALTER TABLE statement. A unique constraint ensures that all values in a column are unique, meaning no two rows can have the same value in that column. Below is the syntax add a unique constraint:
ALTER TABLE table_nameADD CONSTRAINT constraint_name UNIQUE (column_name);
where table_name is the name of the table where you want to add the unique constraint.
constraint_name is the name of the constraint. This is optional, and if omitted, PostgreSQL will generate a name for you.
column_name is the name of the column for which you want to enforce uniqueness.
Let’s say you have a table named students and you want to add a unique constraint on the student_id column to ensure no two students have the same ID:
ALTER TABLE studentsADD CONSTRAINT unique_student_id UNIQUE (student_id);
If you attempt to insert or update a row that would violate the unique constraint, PostgreSQL will raise an error.
Adding a unique constraint requires existing data in the column to be unique. If there are duplicate values in the column, you will need to address those duplicates before adding the constraint.