![]() ![]() Third, attempt to insert a row with the same values in both work_phone and extension columns that already exist in the employees table: INSERT INTO employees(first_name, last_name, work_phone, extension) The statement works because the combination of values in the work_phone and extension column is unique. Second, insert another employee with the same work phone number but a different extension: INSERT INTO employees(first_name, last_name, work_phone, extension) ![]() To test this index, first, insert a row into the employees table: INSERT INTO employees(first_name, last_name, work_phone, extension) To enforce this rule, you can define a UNIQUE index on both work_phone and extension columns: CREATE UNIQUE INDEX idx_employees_workphone ![]() ![]() However, they cannot have the same extension number. Multiple employees can share the same work phone number. The following statement adds two new columns called work_phone and extension to the employees table: ALTER TABLE employees Code language: SQL (Structured Query Language) ( sql ) PostgreSQL UNIQUE index – multiple columns example PostgreSQL issues the following error due to the duplicate mobile phone number: ERROR: duplicate key value violates unique constraint "idx_employees_mobile_phone"ĭETAIL: Key (mobile_phone)=((408)-555-1234) already exists. Second, attempt to insert another row with the same phone number:: INSERT INTO employees(first_name, last_name, email, mobile_phone) 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įirst, insert a new row into the employees table: INSERT INTO employees(first_name, last_name, email, mobile_phone) The following statement adds the mobile_phone column to the employees table: ALTER TABLE employees Here is the output: PostgreSQL UNIQUE index – single column example Tablename = 'employees' Code language: SQL (Structured Query Language) ( sql ) To show indexes of the employees table, you use the following statement: SELECT In this statement, the employee_id is the primary key column and email column has a unique constraint, therefore, PostgreSQL created two UNIQUE indexes, one for each column. The following statement creates a table called employees : CREATE TABLE employees ( When you define a primary key or a unique constraint for a table, PostgreSQL automatically creates a corresponding UNIQUE index. PostgreSQL treats NULLs as distinct values, therefore, you can have multiple NULL values in a column with a UNIQUE index. If you define a UNIQUE index for two or more columns, the combined values in these columns cannot be duplicated in multiple rows. When you define an UNIQUE index for a column, the column cannot store multiple rows with the same values. Note that only B-tree indexes can be declared as unique indexes. To create a UNIQUE index, you can use the following syntax: CREATE UNIQUE INDEX index_name ON table_name( column_name, ) Code language: CSS ( css ) The PostgreSQL UNIQUE index enforces the uniqueness of values in one or multiple columns. Summary: in this tutorial, you will learn how to create a PostgreSQL UNIQUE index to ensure the uniqueness of values in one or more columns. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |