In PostgreSQL, the “DROP CONSTRAINT” clause is used with the ALTER TABLE statement to drop any specific constraint from a table. Using the DROP CONSTRAINT clause, users can drop any specific constraint, such as UNIQUE CONSTRAINT, FOREIGN KEY CONSTRAINT, CHECK CONSTRAINT, and so on.

PostgreSQL's "DROP CONSTRAINT" clause with UNIQUE constraint is explained in this article. The content that illustrates the demonstration is as follows:

• How to DROP UNIQUE CONSTRAINT in PostgreSQL?
• Step 1: Create Table in PostgreSQL
• Step 2: ADD UNIQUE CONSTRAINT in PostgreSQL
• Step 3: DROP UNIQUE CONSTRAINT in PostgreSQL

Let's start with the syntax.

How to DROP UNIQUE CONSTRAINT in PostgreSQL?

In the PostgreSQL database, the “DROP CONSTRAINT” clause removes the rule or policy that is already set using the “ADD CONSTRAINT” clause. To drop unique constraints from a table, users must follow the syntax stated below:

ALTER TABLE tbl_name
DROP CONSTRAINT constraint_name UNIQUE (col_name);

ALTER TABLE is a command in Postgres used to alter/modify a table, while “DROP CONSTRAINT” is a clause that drops the existing unique constraint from the table.

Step 1: Create Table in PostgreSQL

First, create a " college " table with the “CREATE TABLE” statement. Add columns such as std_id, teach_id, sport_date, and notes in this table:

CREATE TABLE college( 
std_id INTEGER NOT NULL,
teach_id INTEGER NOT NULL,
sport_date DATE,
notes VARCHAR(200)
);

The “CREATE TABLE” message verifies that the “college” table has been successfully created.

Step 2: ADD UNIQUE CONSTRAINT in PostgreSQL

In any existing table, such as “college”, a unique constraint can be added using the “ALTER TABLE” statement. For this purpose, write the “ADD CONSTRAINT” clause with a UNIQUE constraint and specify the constraint's name, such as “running”. For this purpose, the statement is as follows:

ALTER TABLE college
ADD CONSTRAINT running UNIQUE   (teach_id);

The "ALTER TABLE" message in the output confirms that the unique constraint has been added to the table. After table alteration, the modified table’s structure will be as follows:

\d college;

The output shows that the UNIQUE constraint has been added to the selected table.

Step 3: DROP UNIQUE CONSTRAINT in PostgreSQL

The “ALTER TABLE” statement is utilized with the “DROP CONSTRAINT” clause to drop the constraint. For instance, the statement is given below:

ALTER TABLE college
DROP CONSTRAINT running;

The constraint “running” in the existing table “college” has been dropped, which can be verified through the “ALTER TABLE” message.

To verify the working of the DROP CONSTRAINT, let’s execute the command mentioned below:

\d college;

The output clearly shows that the unique constraint has been successfully dropped from the “college” table.

That's all from this guide.

Conclusion

Execute the “DROP CONSTRAINT” with the collaboration of the “ALTER TABLE” statement to drop a specific constraint from a table. UNIQUE constraints can be dropped by running the ALTER TABLE command with the "DROP CONSTRAINT" clause followed by the constraint’s name. This blog post presented a step-by-step guide for dropping a unique constraint in Postgres.