In PostgreSQL, the UNIQUE index to ensure the uniqueness of values in one or more columns. To create a UNIQUE index, you can use the following syntax:
Syntax: CREATE UNIQUE INDEX index_name ON table_name(column_name, [...]);
Note: Only B-tree indexes can be declared as unique indexes.
The following statement creates a table called employees :
CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE );
To show the indexes of the employees table, you use the following statement:
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';
The following statement adds the mobile_phone column to the employees table that we created in the above example:
ALTER TABLE employees ADD mobile_phone VARCHAR(20);
To ensure that the mobile phone numbers are distinct for all employees, you define a UNIQUE index for the mobile_phone column as follows:
CREATE UNIQUE INDEX idx_employees_mobile_phone ON employees(mobile_phone);
Now let’s test it. First, insert a new row into the employees table:
INSERT INTO employees(first_name, last_name, email, mobile_phone) VALUES ('Raju', 'kumar', 'email@example.com', '(408)-555-1234');
Second, attempt to insert another row with the same phone number:
INSERT INTO employees(first_name, last_name, email, mobile_phone) VALUES ('Nikhil', 'Aggarwal', 'firstname.lastname@example.org', '(408)-555-1234');
This should raise an error.
As we can observe the behaviour is exactly what is expected of the unique index.
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.