PostgreSQL offers a “DROP CONSTRAINT” clause that allows us to drop any constraint from an existing table. For instance, PRIMARY KEY CONSTRAINT, UNIQUE CONSTRAINT, FOREIGN KEY CONSTRAINT, CHECK CONSTRAINT, or NOT NULL CONSTRAINT.

This blog post will explain how to drop a constraint in PostgreSQL through Practical examples. So, let’s begin!

How to Drop a Constraint in Postgres?

To drop any particular constraint from a Postgres table, users must execute the DROP CONSTRAINT with the ALTER TABLE command.

The following content will be covered in this section regarding adding or dropping a CONSTRAINT in PostgreSQL:

• Create a Sample Table
• How to Drop a PRIMARY KEY CONSTRAINT in Postgres?
• How to Drop a FOREIGN KEY CONSTRAINT in Postgres?
• How to Drop a UNIQUE CONSTRAINT in Postgres?
• How to Drop a CHECK CONSTRAINT in Postgres?
• How to Remove/Drop a NOT NULL CONSTRAINT From a Postgres Table?

Create a Sample Table

Firstly, we will create a sample table with some columns without any constraints. Afterward, we will add and drop a specific constraint according to the requirement.

CREATE TABLE staff_bio(
st_id INT,
st_name TEXT,
st_department TEXT,
st_age SMALLINT
);

The table name staff_bio has been created with four columns: st_id, st_name, st_department, and st_age.

How to Drop a PRIMARY KEY CONSTRAINT in Postgres?

In Postgres, Primary keys are used to identify a table’s record uniquely. This section will teach you how to add or drop a PRIMARY KEY CONSTRAINT in Postgres Using ALTER TABLE command.

Step 1: Add Primary Key Constraint

Firstly, let’s add a primary key constraint on the “st_id” column using ALTER TABLE command and ADD CONSTRAINT clause:

ALTER TABLE staff_bio
ADD CONSTRAINT st_id_pk 
PRIMARY KEY (st_id);

In the above snippet,

- The “staff_bio” is a table to be altered.
- The “st_id_pk” represents the name of the primary key.
- The “st_id” represents a primary key column.

The “ALTER TABLE” message in the output window proves that the “staff_bio” table has been modified successfully. Let’s validate the table’s structure via the following command:

SELECT * FROM staff_bio;

This way, you can add a primary key to any table’s column.

Step 2: Drop Primary Key Constraint

The DROP CONSTRAINT clause can be used in conjunction with ALTER TABLE to drop a primary key constraint from a Postgres table.

ALTER TABLE staff_bio
DROP CONSTRAINT st_id_pk;

In this coding example, we dropped a primary key constraint named st_id_pk from the staff_bio table:

Let’s verify the constraint deletion via the below command:

SELECT * FROM staff_bio;

The output clarifies that the primary key constraint has been removed successfully.

How to Drop a FOREIGN KEY CONSTRAINT in Postgres?

A FOREIGN KEY is a column that points to the PRIMARY KEY of some other Postgres table.

In this section, we will learn how to add or drop a FOREIGN KEY CONSTRAINT in Postgres Using ALTER TABLE command.

To explain the concept of foreign key constraint, we will use the “staff_info” and “employee_info” tables, whose details are shown below:

SELECT * FROM customers_info;

SELECT * FROM orders_details;

Step 1: Add Foreign Key Constraint

Suppose we want to refer the “customer_id” column from the “orders_details” table to the “c_id” column of the “order_details” table. To do that, let’s run the below statement:

ALTER TABLE orders_details
ADD CONSTRAINT fk_ord_cust 
FOREIGN KEY (customer_id) REFERENCES customers_info (c_id);

The output snippet shows that the foreign key constraint has been added to the orders_details table successfully.

Step 2: Drop Foreign Key Constraint

To drop a foreign key constraint from a table, use the ALTER TABLE with the DROP CONSTRAINT clause:

ALTER TABLE orders_details
DROP CONSTRAINT fk_ord_cust;

The “ALTER TABLE” message in the output window proves that the foreign key named “fk_ord_cust” has been dropped successfully.

How to Drop a UNIQUE CONSTRAINT in Postgres?

A UNIQUE constraint in Postgres ensures that all the rows in a column are unique. When a new record is inserted in a table, the UNIQUE constraint checks whether or not the input record already exists in the targeted table.

Step 1: Add a UNIQUE Constraint

To add a UNIQUE constraint on the “st_id” column, use the ALTER TABLE command alongside ADD CONSTRAINT clause:

ALTER TABLE staff_bio
ADD CONSTRAINT st_id_unique UNIQUE (st_id);

In the above snippet,

- The “staff_bio” is a table to be altered.
- The “st_id_unique” represents unique constraint.
- The UNIQUE constraint will be applied to the column named "st_id".

Let’s verify the working of the UNIQUE constraint by adding a couple of duplicate records in the st_id column:

INSERT INTO staff_bio(st_id, st_name, st_department, st_age)
VALUES (1, 'Joe', 'HR', 24),
(1, 'Joseph', 'Author', 26);

The stated error proves that duplicate entries can’t be inserted into a column that is created with a UNIQUE CONSTRAINT.

Step 2: DROP a UNIQUE Constraint

A UNIQUE constraint can be dropped from a column using the DROP CONSTRAINT clause with ALTER TABLE statement.

ALTER TABLE staff_bio
DROP CONSTRAINT st_id_unique;

In the above snippet:

- staff_bio is a table to be altered/modified.
- st_id_unique is a unique constraint that needs to be dropped from the staff_bio table.

To verify if the UNIQUE constraint is dropped or not, insert a couple of duplicate records in the st_id column. If the column accepts the duplicate records, this means the UNIQUE constraint has been dropped:

INSERT INTO staff_bio(st_id, st_name, st_department, st_age)
VALUES (1, 'Joe', 'HR', 24),
(1, 'Joseph', 'Author', 26);

Two records with the same “st_id” have been inserted into the staff_bio table. It proves that the UNIQUE constraint has been removed from the st_id column.

How to Drop a CHECK CONSTRAINT in Postgres?

In Postgres, the CHECK constraint is used to insert/update values into a column based on some particular condition.

Step 1: Add CHECK Constraint

Execute the ALTER TABLE alongside ADD CONSTRAINT clause to add a check constraint to a table’s column:

ALTER TABLE staff_bio
ADD CONSTRAINT check_st_age 
CHECK (st_age <= 20);

In the above snippet:

- staff_bio is a table to be modified.
- check_st_age represents the name of the check constraint.
- CHECK (st_age <= 20) means the st_age column will accept only values less than or equal to 20.

Let’s verify the working of the CHECK constraint by adding a value greater than 20 in the st_age column:

INSERT INTO staff_bio(st_id, st_name, st_department, st_age)
VALUES (1, 'Joe', 'HR', 24);

The stated error proves that only those entries will be accepted by the st_age column that satisfies the specified condition for the CHECK CONSTRAINT.

Step 2: Drop CHECK Constraint

To drop a CHECK constraint from a table’s column, use the DROP CONSTRAINT clause with the collaboration of the ALTER TABLE command.

ALTER TABLE staff_bio
DROP CONSTRAINT check_st_age;

In the above snippet, check_st_age is a constraint (check) to be dropped:

To verify if the CHECK constraint is dropped, insert a value greater than 20 in the st_age column. If the column accepts the specified value, this means the CHECK constraint has been dropped:

INSERT INTO staff_bio(st_id, st_name, st_department, st_age)
VALUES (1, 'Joe', 'HR', 24);

The st_age column accepts a value greater than 20. It proves that the CHECK constraint has been dropped successfully.

How to Remove/Drop a NOT NULL CONSTRAINT From a Postgres Table?

In PostgreSQL, a table column created with a NOT NULL constraint accepts only non-null values. This section will teach you how to add or drop a NOT NULL constraint in Postgres using ALTER TABLE command.

Step 1: Add NOT NULL Constraint

ALTER TABLE statement with ALTER COLUMN clause is used in Postgres to add NOT NULL constraints to the columns of any existing table:

ALTER TABLE staff_bio
ALTER COLUMN st_name SET NOT NULL;

In the above snippet:

- The “staff_bio” is a table to be altered/modified.
- The “st_name” represents a column to be modified.
- The SET clause is used to add a NOT NULL constraint on the st_name column:

The output snippet verifies that the table has been altered successfully. Let’s insert a null value into the st_name column to comprehend the working of the NOT NULL constraint:

INSERT INTO staff_bio(st_name)
VALUES (NULL);

The output proves that a null value can’t be inserted into a column declared with a NOT NULL constraint.

Step 2: Drop NOT NULL Constraints

A NOT NULL constraint can be dropped from a column via the ALTER TABLE statement and the ALTER COLUMN clause:

ALTER TABLE staff_bio
ALTER COLUMN st_name DROP NOT NULL;

In this coding example, the ALTER TABLE and ALTER COLUMN statements are used to remove/drop the NOT NULL constraint from the "st_name" column:

The output snippet verifies that the table has been altered successfully. Inserting a null value into the st_name column will assist you in understanding the working of the NOT NULL constraint:

INSERT INTO staff_bio(st_name)
VALUES (NULL);

A NULL value has been inserted into the st_name column. It proves that the NOT NULL constraint has been dropped successfully.

That’s it from this Postgres blog!

Conclusion

In Postgres, Users must execute the DROP CONSTRAINT with the ALTER TABLE command to drop any particular constraint from a Postgres table. A NOT NULL constraint can be dropped from a column via the ALTER TABLE statement and the ALTER COLUMN clause. This blog has presented a thorough overview of how to use DROP CONSTRAINTS in PostgreSQL using practical examples.