PostgreSQL – Unique Constraint
PostgreSQL supports unique constraints to ensure that the values in a particular column ( or set of columns ) are unique across all rows in a table. Unique constraint can be defined on one or more columns of a table.
To create a unique constraint in PostgreSQL, you can use the UNIQUE keyword followed by the name of column(s) you would like to apply the constraint on. For example, to create a unique constraint on a column called email in a table users, you can use the following SQL statement:
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
This will create a unique constraint named unique_email on the email column of the users table.
If you try to insert a row with a value in the email column that already exists in the table, PostgreSQL will raise an error.
You can also create a unique constraint on multiple columns by listing them within the parentheses separated by commas:
ALTER TABLE users ADD CONSTRAINT unique_name_email UNIQUE (name, email);
Above sql will create a unique constraint on the combination of the name and email columns in the users table. This constraint will make sure that no two rows have the same combination of values in those columns.
Unique constraints can be dropped using the DROP CONSTRAINT command. Syntax to drop a constraint is as shown below:
ALTER TABLE users DROP CONSTRAINT unique_email;