PostgreSQL - UNIQUE Constraint

The UNIQUE constraint in PostgreSQL is used to ensure that all values in a column (or a combination of multiple columns) are unique across all rows in the table. In other words, no two rows can have the same value in a column with a UNIQUE constraint.

Key Points:

Usage:

When defining a table:

CREATE TABLE table_name (
   column1 data_type UNIQUE,
   column2 data_type,
   ...
);

Or, for multiple columns:

CREATE TABLE table_name (
   column1 data_type,
   column2 data_type,
   UNIQUE (column1, column2)
);

Adding to an Existing Table:
You can add a UNIQUE constraint to an existing table using the ALTER TABLE statement:
```
ALTER TABLE table_name ADD UNIQUE (column_name);
```
Or, for multiple columns:
```
ALTER TABLE table_name ADD UNIQUE (column1, column2);
```
Dropping a UNIQUE Constraint:
To drop a UNIQUE constraint:
```
ALTER TABLE table_name DROP CONSTRAINT unique_constraint_name;
```
The unique_constraint_name can usually be found from the table's schema details or by querying the database's information schema.
UNIQUE vs. PRIMARY KEY:
While both the UNIQUE constraint and the PRIMARY KEY constraint ensure uniqueness, a table can have multiple UNIQUE constraints but only one PRIMARY KEY. Additionally, the PRIMARY KEY implicitly creates a unique B-tree index on the column, and it cannot contain NULL values, whereas a column with a UNIQUE constraint can have NULL values (but only one NULL is allowed).
Index Creation:
When you add a UNIQUE constraint, PostgreSQL automatically creates a unique B-tree index on the column or a group of columns used in the constraint. This index not only enforces the uniqueness but can also speed up queries that search for unique column values.
Violation & Error Handling:
If you attempt to insert or update a record in a way that would violate the UNIQUE constraint, PostgreSQL will raise a unique violation error, and the operation will fail.
Considerations:
- Before adding a UNIQUE constraint to an existing table, ensure the current data adheres to the constraint; otherwise, the addition will fail.
- A UNIQUE constraint allows multiple NULL values in PostgreSQL unless the column also has a NOT NULL constraint.
- When defining a unique constraint on multiple columns, the combination of values in these columns must be unique for every row. Individual columns can still contain duplicate values.

In summary, the UNIQUE constraint in PostgreSQL is a powerful tool to ensure data integrity by preventing duplicate values in specific columns or combinations of columns. Proper usage and understanding of this constraint can help maintain data consistency and quality in your database.

PostgreSQL UNIQUE constraint example: Use the UNIQUE constraint to enforce uniqueness on a column:

CREATE TABLE example_table (
   id serial PRIMARY KEY,
   unique_column INTEGER UNIQUE,
   other_column VARCHAR(50)
);

How to add UNIQUE constraint in PostgreSQL: Add a UNIQUE constraint to an existing column:
```
ALTER TABLE example_table
ADD CONSTRAINT unique_constraint UNIQUE (existing_column);
```

PostgreSQL UNIQUE constraint on multiple columns: Create a UNIQUE constraint on multiple columns:

CREATE TABLE example_table (
   id serial PRIMARY KEY,
   column1 INTEGER,
   column2 VARCHAR(50),
   CONSTRAINT unique_constraint UNIQUE (column1, column2)
);

Remove UNIQUE constraint in PostgreSQL: Drop a UNIQUE constraint from a column:
```
ALTER TABLE example_table
DROP CONSTRAINT unique_constraint;
```
Check if a column has UNIQUE constraint in PostgreSQL: Query the pg_constraint system catalog to check if a column has a UNIQUE constraint:
```
SELECT conname FROM pg_constraint WHERE conrelid = 'example_table'::regclass;
```

PostgreSQL composite UNIQUE constraint: Create a composite UNIQUE constraint on multiple columns:

CREATE TABLE example_table (
   id serial PRIMARY KEY,
   column1 INTEGER,
   column2 VARCHAR(50),
   CONSTRAINT unique_constraint UNIQUE (column1, column2)
);

Enforce case-insensitive UNIQUE constraint in PostgreSQL: Use a case-insensitive collation to enforce case-insensitive uniqueness:
```
CREATE TABLE example_table (
   id serial PRIMARY KEY,
   case_insensitive_column VARCHAR(50) UNIQUE COLLATE "en_US" 
);
```
ALTER TABLE ADD UNIQUE constraint in PostgreSQL: Add a UNIQUE constraint using ALTER TABLE:
```
ALTER TABLE example_table
ADD CONSTRAINT unique_constraint UNIQUE (existing_column);
```