How to drop a primary key in SQL?

Syntax

To drop a primary key using the ALTER TABLE command, here is the generic syntax:

• For MySQL:

ALTER TABLE table_name
DROP PRIMARY KEY;

• For PostgreSQL, Oracle, and MS SQL Server:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Example

We want to drop the primary key from the table employees in MS SQL Server database.

If you didn’t specify name of the primary key constraint, it’s auto generated and now you have to find its name first:

SELECT name
FROM   sys.key_constraints
WHERE  [type] = 'PK'
       AND [parent_object_id] = Object_id('dob.employees');

Let’s say it returns the constraint name pk_employeeid, now drop it:

ALTER TABLE dob.employees
DROP CONSTRAINT pk_employeeid;

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS