Back to cookbooks list Articles Cookbook

How to remove a unique constraint in SQL?

  • DROP CONSTRAINT
  • ALTER TABLE

Problem:

You would like to remove a unique constraint from the column/columns in a table in a database.

Example:

We would like to drop the unique constraint in the table product from the column name. The statement below shows how to do it.

Solution

ALTER TABLE product 
DROP CONSTRAINT UQ_product_name

In this example the table product is altered by using the ALTER TABLE clause. After this clause you enter the name of the table (in our example: product) and the clause DROP CONSTRAINT with the name of the unique constraint you want to remove.

You can find the name of the constraint in the meta data of your database. Each database engine has a different method of naming constraints. In SQL Server, you can check the name of the constraint by selecting data from the sys.key_constraints table in a given database. In PostgreSQL, select the conname column from the pg_constraint table. The Oracle server contains this data in the user_constraints table, and MySQL allows getting names from the CONSTRAINT_NAME column in information_schema.TABLE_CONSTRAINTS.

Recommended courses:

Recommended articles:

See also: