July 6, 2024 By Matthew Rathbone *

To drop a constraint in PostgreSQL, use the ALTER TABLE command followed by
the DROP CONSTRAINT clause, specifying the name of the constraint to be
dropped. Be careful, as this can potentially lead to data integrity issues.

Basic Syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

A Database Manager That Is Modern, Fast, & Easy To Use

Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan

I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

Types of Constraints in PostgreSQL

Before learning how to drop constraints, it’s good to know the common types of available constraints:

  • Primary Key
  • Foreign Key
  • Unique
  • Check

Dropping a Primary Key Constraint

A primary key uniquely identifies each record in a table.


Let’s say we have a table, users:

    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL

To drop the primary key constraint:


Expected Output:


In this case, users_pkey is the name of the constraint that you want to drop. You can find the name of the constraint by using the \d command in the psql terminal with the name of the table where you wish to view the constraints, or by viewing it in the Indexes tab in Beekeeper Studio, which will show you the details of the table, including the name of the constraints.

Dropping a Foreign Key Constraint

Foreign keys enforce referential integrity between two tables. Here’s how to drop a foreign key constraint.


Consider two tables, orders and customers:

CREATE TABLE customers (
    customer_id serial PRIMARY KEY,
    customer_name VARCHAR (100) NOT NULL

    order_id serial PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT,
    CONSTRAINT fk_customer
        FOREIGN KEY(customer_id) 
        REFERENCES customers(customer_id)

To drop the foreign key constraint fk_customer:

DROP CONSTRAINT fk_customer;

Expected Output:


See How to Drop a Foreign Key Constraint for more.

Dropping a Unique Constraint

Unique constraints ensure all values in a column are different. Dropping a unique constraint is similar to dropping other constraints.


Given the users table:

    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL

To drop the unique constraint on the username column:

DROP CONSTRAINT users_username_key;

Expected Output:


See Creating Unique Constraints in PostgreSQL

Dropping a Check Constraint

Check constraints enforce domain integrity by limiting the values that can be placed in a column.


Suppose we have a products table:

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    price DECIMAL (10, 2) NOT NULL,
    CONSTRAINT price_check CHECK (price > 0)

To drop the check constraint price_check:

ALTER TABLE products
DROP CONSTRAINT price_check;

Expected Output:


Data is not affected

It’s worth noting that when you drop a constraint, the underlying data in the table is not affected. The constraint is simply removed, and you will be able to insert or update data in the table without having to comply with the constraints that were previously in place.

Dropping constraints can cause integrity issues

It is important to be careful when dropping constraints, as doing so can lead to data integrity issues if the constraints were in place for a reason. For example, if you drop a primary key constraint, you may end up with multiple rows in the table that have the same values in the primary key column, which can cause problems when you try to query or update the data in the table.

Therefore, it’s always a good idea to thoroughly review your data and the constraints that are in place before dropping any constraints in your database. You should also consider the potential impact on any applications or processes that rely on the data in the table before proceeding with the drop.


Dropping constraints in PostgreSQL involves using the ALTER TABLE command followed by DROP CONSTRAINT. Each type of constraint—primary key, foreign key, unique, and check—can be dropped by specifying its name. Be careful when doing this, as it can potentially lead to data integrity issues if the constraint was in place for a valid reason. Understanding how to manage constraints effectively allows you to maintain and evolve your database schema over time.