![]() ![]() Replace the “name_of_table” with the desired table name and “name_of_constraint” with the constraint names that need to be removed.Įxample: Removing Unique Constraint From Multiple Columns PostgreSQL uses the ALTER TABLE command with the DROP CONSTRAINT clause to remove/drop the uniqueness from single or multiple columns: ALTER TABLE name_of_table How to Remove/DROP a UNIQUE Constraint From Multiple Columns of a PostgreSQL Table Users can confirm the table alteration by executing the below-provided command: \d emp_info įrom the output, it is clear that the UNIQUE CONSTRAINT has been successfully applied to the selected columns. The selected table has been successfully altered: For that purpose, we will execute the ALTER TABLE command with the UNIQUE constraint as follows: ALTER TABLE emp_infoĪDD CONSTRAINT unique_constraints UNIQUE (emp_id, emp_name) Suppose we have to apply a UNIQUE constraint on the “emp_id” and “emp_name” columns. We have an already existing table named “emp_info” having the following structure: \d emp_info The “col_name_1, col_name_2, …” that are enclosed within a set of parentheses represent the columns on which the unique constraint will be applied.Įxample: Applying UNIQUE Constraint on Existing Table “ADD CONSTRAINT” is a clause that applies uniqueness to the desired table columns. “name_of_table” indicates an existing table that needs to be altered. ![]() Use the below-provided syntax to add/set a UNIQUE constraint on various columns of an existing PostgreSQL table: ALTER TABLE name_of_tableĪDD CONSTRAINT constraint_name UNIQUE (col_name_1, col_name_2, …) How to Add a UNIQUE Constraint on Multiple Columns of an Existing Postgres Table? VALUES (1, 'Joseph', a duplicate record throws an error which indicates that the duplicate key value violates the unique constraint: Now let’s try to insert a duplicate record into the “cp_employee” table and see how the UNIQUE constraint deals with that particular situation: INSERT INTO cp_employee(emp_id, emp_name, emp_email) VALUES (1, 'Joseph', 'Henry', output snippet shows that two records have been successfully inserted into the “cp_employee” table: Let’s insert a couple of records to the newly created “cp_employee” table using the following command: INSERT INTO cp_employee(emp_id, emp_name, emp_email) The UNIQUE constraint has been successfully applied to the “emp_id” and “emp_email” columns of the “cp_employee” table. The following snippet indicates that the “cp_employee” table has been created with the desired columns: Let’s create a “ cp_employee” table and insert the unique constraint on a couple of its columns: CREATE TABLE cp_employee( The “col_name_1, col_name_2, …” that are enclosed within a set of parentheses represent the columns on which the unique constraint will be applied.Įxample: Applying Unique Constraint on Multiple Columns UNIQUE is a keyword that is used to apply the UNIQUE constraint on the table’s columns. data_type represents any valid Postgres data type, such as INT, TEXT, VARCHAR, etc. ![]() “ col_name_1, col_name_2, …, col_name_N” are the table columns to be created. For this purpose, all you have to do is, follow the syntax provided below: CREATE TABLE name_of_table( Postgres enables its users to add/create a UNIQUE Constraint on multiple columns of a Postgres table while table creation. How to Add a UNIQUE Constraint on Multiple Columns of a Postgres Table This post illustrates a comprehensive knowledge of adding or removing unique constraints from multiple columns. By using the UNIQUE constraint on single or multiple columns, users can make sure that each value in those columns is unique and not repeated. It ensures the uniqueness/originality of the table records or values. The UNIQUE constraint is a popularly used concept in PostgreSQL that helps us keep data precise, clean, and organized. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |