Can You Delete a Primary Key in SQL?

Short answer: Yes, absolutely.

Surprising answer: Most developers don’t know this is even possible.

Here’s the thing—primary keys feel permanent. You set them when creating a table, and they just… stay there. But SQL lets you remove them completely. The real question isn’t “can you,” it’s “what happens when you do?”

Let me show you what actually happens when you delete a primary key.

The Simple Answer: Yes, With One Command

-- Create a table with primary key
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- Delete the primary key
ALTER TABLE customers DROP PRIMARY KEY;

-- Success! The primary key is gone

It works. The table still exists. The data is intact. But now something critical is missing.

What Really Happens When You Remove a Primary Key

Before Deletion:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

INSERT INTO products (product_name, price) VALUES ('Laptop', 999.99);
INSERT INTO products (product_name, price) VALUES ('Mouse', 29.99);

SELECT * FROM products;

Output:

product_id | product_name | price
-----------|--------------|-------
1          | Laptop       | 999.99
2          | Mouse        | 29.99

After Deleting Primary Key:

ALTER TABLE products DROP PRIMARY KEY;

-- Now you can insert duplicate IDs
INSERT INTO products VALUES (1, 'Keyboard', 49.99);

SELECT * FROM products;

Output:

product_id | product_name | price
-----------|--------------|-------
1          | Laptop       | 999.99
2          | Mouse        | 29.99
1          | Keyboard     | 49.99  -- DUPLICATE!

The Problem: No more uniqueness guarantee. Your data integrity just disappeared.

But Wait—There’s a Catch with Foreign Keys

Here’s where it gets interesting:

-- Parent table
CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(100)
);

-- Child table referencing parent
CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- Try to delete the primary key
ALTER TABLE authors DROP PRIMARY KEY;
```

Error:

ERROR 1025: Error on rename of './database/authors' 
(errno: 150 "Foreign key constraint is incorrectly formed")

What Just Happened: SQL blocked you. The books table depends on authors.author_id being unique. The database won’t let you break that relationship.

So the real answer is: You can delete a primary key, but only if nothing depends on it.

Different Databases, Different Syntax

MySQL:

ALTER TABLE table_name DROP PRIMARY KEY;

PostgreSQL:

-- First, find the constraint name
SELECT constraint_name 
FROM information_schema.table_constraints 
WHERE table_name = 'table_name' AND constraint_type = 'PRIMARY KEY';

-- Then drop it
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

SQL Server:

-- Find constraint name
SELECT name 
FROM sys.key_constraints 
WHERE type = 'PK' AND parent_object_id = OBJECT_ID('table_name');

-- Drop it
ALTER TABLE table_name DROP CONSTRAINT PK_constraint_name;

When You Actually Need to Delete a Primary Key

Scenario 1: Wrong Column Was Made Primary

-- Oops! Made email the primary key instead of user_id
CREATE TABLE users (
    user_id INT,
    email VARCHAR(100) PRIMARY KEY,
    username VARCHAR(50)
);

-- Fix it
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (user_id);

Scenario 2: Converting to Composite Primary Key

-- Single column primary key
CREATE TABLE student_courses (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT
);

-- Need to change to composite key
ALTER TABLE student_courses DROP PRIMARY KEY;
ALTER TABLE student_courses ADD PRIMARY KEY (student_id, course_id);

Scenario 3: Database Migration/Redesign

-- Old design: Natural key
CREATE TABLE employees (
    ssn VARCHAR(11) PRIMARY KEY,
    name VARCHAR(100)
);

-- Migrate to surrogate key
ALTER TABLE employees DROP PRIMARY KEY;
ALTER TABLE employees ADD COLUMN employee_id INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE employees ADD UNIQUE KEY (ssn);

The Safe Way to Delete a Primary Key with Foreign Keys

You must drop foreign keys first, then the primary key, then recreate everything:

-- Step 1: Find foreign key constraint name
SELECT CONSTRAINT_NAME 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'books' AND COLUMN_NAME = 'author_id';

-- Step 2: Drop the foreign key
ALTER TABLE books DROP FOREIGN KEY books_ibfk_1;

-- Step 3: Now you can drop the primary key
ALTER TABLE authors DROP PRIMARY KEY;

-- Step 4: Recreate primary key on correct column
ALTER TABLE authors ADD PRIMARY KEY (new_column);

-- Step 5: Recreate the foreign key
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(new_column);

Complete Real-World Example: Fixing a Design Flaw

The Situation: An e-commerce site used product SKU as primary key, but SKUs sometimes change for rebranding.

-- Original problematic design
CREATE TABLE products (
    sku VARCHAR(50) PRIMARY KEY,
    product_name VARCHAR(200),
    price DECIMAL(10,2)
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_sku VARCHAR(50),
    quantity INT,
    FOREIGN KEY (product_sku) REFERENCES products(sku)
);

-- Insert some data
INSERT INTO products VALUES ('LAPTOP-2024', 'Gaming Laptop', 1299.99);
INSERT INTO order_items (order_id, product_sku, quantity) VALUES (1, 'LAPTOP-2024', 2);

The Problem: When marketing wants to change SKU to ‘LAPTOP-2025’, you can’t—it breaks all order history.

The Fix:

-- Step 1: Drop foreign key constraint
ALTER TABLE order_items DROP FOREIGN KEY order_items_ibfk_1;

-- Step 2: Add new surrogate key column to products
ALTER TABLE products ADD COLUMN product_id INT AUTO_INCREMENT UNIQUE FIRST;

-- Step 3: Add matching column to order_items
ALTER TABLE order_items ADD COLUMN product_id INT;

-- Step 4: Populate the new foreign key column
UPDATE order_items oi
JOIN products p ON oi.product_sku = p.sku
SET oi.product_id = p.product_id;

-- Step 5: Drop old primary key
ALTER TABLE products DROP PRIMARY KEY;

-- Step 6: Make product_id the new primary key
ALTER TABLE products ADD PRIMARY KEY (product_id);

-- Step 7: Make SKU unique but not primary
ALTER TABLE products ADD UNIQUE KEY (sku);

-- Step 8: Drop old foreign key column
ALTER TABLE order_items DROP COLUMN product_sku;

-- Step 9: Create new foreign key relationship
ALTER TABLE order_items ADD FOREIGN KEY (product_id) REFERENCES products(product_id);

Result:

-- Now you can change SKUs without breaking references
UPDATE products SET sku = 'LAPTOP-2025' WHERE product_id = 1;
-- Works perfectly! Order history preserved.

SELECT * FROM products;
```

Output:

product_id | sku          | product_name  | price
-----------|--------------|---------------|--------
1          | LAPTOP-2025  | Gaming Laptop | 1299.99

The AUTO_INCREMENT Complication

Important caveat:

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('First');
-- id = 1 (auto-generated)

ALTER TABLE test DROP PRIMARY KEY;

Error:

ERROR 1075: Incorrect table definition; 
there can be only one auto column and it must be defined as a key

Why: AUTO_INCREMENT requires an index (and primary keys are indexes). Remove the primary key, and AUTO_INCREMENT breaks.

The Fix:

-- Remove AUTO_INCREMENT first
ALTER TABLE test MODIFY id INT;

-- Then drop primary key
ALTER TABLE test DROP PRIMARY KEY;

-- Now it works

Can You Have a Table Without a Primary Key?

Yes, technically:

CREATE TABLE logs (
    log_message TEXT,
    log_time TIMESTAMP
);

-- No primary key at all
-- Perfectly valid SQL

But here’s what you lose:

• No guaranteed way to identify unique rows
• Slower queries (no automatic index on a key column)
• Can’t be referenced by foreign keys
• Difficult to UPDATE or DELETE specific rows

Real example of the problem:

INSERT INTO logs VALUES ('Error occurred', NOW());
INSERT INTO logs VALUES ('Error occurred', NOW());

-- How do you delete just ONE of these identical rows?
DELETE FROM logs WHERE log_message = 'Error occurred' LIMIT 1;
-- You can't control which one gets deleted!

The Bottom Line

Can you delete a primary key in SQL?

Yes. One ALTER TABLE command and it’s gone.

Will your database let you?

Only if no foreign keys depend on it. Otherwise, you’ll get an error and need to drop those foreign keys first.

Should you delete it?

Only when you have a replacement ready. Deleting a primary key without adding a new one is like removing your car’s steering wheel mid-drive—technically possible, but you’ll crash immediately.