How to Delete a Primary Key in SQL: A Practical Guide for Oracle, SQL Server, MySQL, and PostgreSQL

Today, I’m excited to share a practical guide on how to delete a primary key across four popular databases: Oracle, SQL Server, MySQL, and PostgreSQL. Whether you’re diving into SQL for the first time or prepping for your next database interview, I’ll walk you through each process step-by-step.

Part 1: Deleting a Primary Key in Oracle Database

First up is Oracle. I believe the process is straightforward once you know the steps. Here’s how I delete a primary key constraint.

  1. Identify the Primary Key Constraint

In Oracle, the first step is to identify the name of the primary key constraint. Here’s the query I use:


SELECT constraint_name
FROM user_constraints
WHERE table_name = 'YOUR_TABLE_NAME'
AND constraint_type = 'P';

This query starts with SELECT constraint_name, which helps me retrieve the name of the primary key constraint. I select from the user_constraints table, which stores all constraint details.

In the WHERE clause:

  • table_name = 'YOUR_TABLE_NAME' limits the results to my specific table.
  • constraint_type = 'P' ensures I’m filtering for primary key constraints only.

This query gives me the exact name of my table’s primary key constraint, which is essential for deleting it. I always make sure to replace 'YOUR_TABLE_NAME' with my actual table name. For example, if my table name is ‘EMPLOYEES_DEMO,’ this query will return the precise constraint name I need.

  1. Delete the Primary Key Constraint

    Once I have the constraint name, here’s the command I use to drop the primary key:


ALTER TABLE YOUR_TABLE_NAME
DROP CONSTRAINT YOUR_CONSTRAINT_NAME;

This command starts with ALTER TABLE YOUR_TABLE_NAME, indicating which table I want to modify. The DROP CONSTRAINT YOUR_CONSTRAINT_NAME portion tells SQL to remove the specific primary key constraint I identified earlier.

I replace YOUR_TABLE_NAME and YOUR_CONSTRAINT_NAME with my actual table and constraint names, and executing this command deletes the primary key from my table.

By the way, if you’d like to copy these commands directly, I’ve prepared a detailed blog post on this topic with all the commands ready to go. Just check the link in the description below!

Part 2: Deleting a Primary Key in SQL Server

Next, let’s move on to SQL Server. From my experience, the process is quite similar.

  1. Check the Primary Key Constraint Name

    In SQL Server, I can find the primary key constraint using this query:


SELECT name
FROM sys.key_constraints
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('YOUR_TABLE_NAME');

This query starts with SELECT name, which retrieves the name of the primary key constraint. I select from sys.key_constraints, a system table that holds all key constraints in SQL Server.

In the WHERE clause:

  • type = 'PK' filters for primary keys only.
  • parent_object_id = OBJECT_ID('YOUR_TABLE_NAME') limits results to my specified table.

I always remember to replace 'YOUR_TABLE_NAME' with my actual table name. This query gives me the primary key constraint name for my table, which I’ll need to delete it.

  1. Drop the Primary Key Constraint

    Once I have the constraint name, I use this command to drop it:


ALTER TABLE YOUR_TABLE_NAME
DROP CONSTRAINT YOUR_CONSTRAINT_NAME;

just like in Oracle, replacing the constraint name is crucial. And that’s it—I’ve successfully removed my primary key in SQL Server!

Part 3: Deleting a Primary Key in MySQL

Now, let’s take a look at how MySQL handles primary keys. I’ve found it to be quite user-friendly.

  1. Find the Primary Key

    In MySQL, primary keys are typically named automatically. If you haven’t renamed it, it’s often just PRIMARY. However, to be sure, here’s the query I check:


SHOW KEYS FROM YOUR_TABLE_NAME WHERE Key_name = 'PRIMARY';

This query starts with SHOW KEYS FROM YOUR_TABLE_NAME, listing all keys (indexes and constraints) for the specified table.

By adding WHERE Key_name = 'PRIMARY', I filter the results to only show the primary key. This gives me the primary key details I need for the next steps.

  1. Delete the Primary Key

    To drop the primary key in MySQL, I use this command:


ALTER TABLE YOUR_TABLE_NAME
DROP PRIMARY KEY;

I love how I don’t need to specify a constraint name here—just run this command, and MySQL will drop the primary key for me!

Part 4: Deleting a Primary Key in PostgreSQL

Finally, let’s tackle PostgreSQL. This database has a process similar to Oracle’s, which I appreciate.

  1. Get the Constraint Name

    I first need to find the primary key constraint name with this query:


SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = 'your_table_name'
AND constraint_type = 'PRIMARY KEY';

This query begins with SELECT constraint_name, retrieving the name of the primary key constraint. I’m querying the information_schema.table_constraints view, which contains metadata about all table constraints in PostgreSQL.

In the WHERE clause:

  • table_name = 'your_table_name' specifies the table I’m interested in.
  • constraint_type = 'PRIMARY KEY' filters for primary key constraints only.

This query returns the name of the primary key constraint for my specified table, which is essential for deletion.

  1. Drop the Primary Key

    With the constraint name in hand, I use this command to drop it:


ALTER TABLE your_table_name
DROP CONSTRAINT your_constraint_name;

And just like that, I’ve removed a primary key in PostgreSQL!

Conclusion

And there you have it! I’ve covered how to delete primary keys in four major databases—Oracle, SQL Server, MySQL, and PostgreSQL. I hope you found this guide practical and easy to follow. If you did, please consider liking, subscribing, and sharing this blog post with anyone who might benefit from these SQL tips. Thanks for reading, and I look forward to sharing more insights with you soon!

RELATED ARTICLESMORE FROM AUTHOR