Cannot add a PRIMARY KEY column

This error occurs when you try to add a PRIMARY KEY column to an existing table using ALTER TABLE. SQLite has limited ALTER TABLE support.

The Cannot add a PRIMARY KEY column error occurs because SQLite's ALTER TABLE is limited.

Understanding the Error

Error: Cannot add a PRIMARY KEY column

SQLite's ALTER TABLE only supports adding columns with certain constraints, not PRIMARY KEY.

What ALTER TABLE Supports

SQLite's ALTER TABLE can:

Rename table
Rename column
Add column (with restrictions)

Cannot:

Add PRIMARY KEY
Add UNIQUE constraint
Add FOREIGN KEY
Remove column (before 3.35.0)
Modify column type

How to Fix It

Solution 1: Recreate Table

The standard approach:

SQL

-- 1. Create new table with desired schema
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,  -- The new primary key
  name TEXT,
  email TEXT
);

-- 2. Copy data
INSERT INTO users_new (name, email)
SELECT name, email FROM users;

-- 3. Drop old table
DROP TABLE users;

-- 4. Rename new table
ALTER TABLE users_new RENAME TO users;

Solution 2: With Foreign Keys

Handle foreign key references:

SQL

-- Disable foreign keys during migration
PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;

-- Recreate with new schema
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO users_new (name) SELECT name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

-- Update referencing tables if needed
UPDATE posts SET user_id = (
  SELECT id FROM users WHERE name = (
    SELECT name FROM users_old WHERE id = posts.user_id
  )
);

COMMIT;

PRAGMA foreign_keys = ON;

Solution 3: Preserve Indexes and Triggers

SQL

-- Save existing indexes
SELECT sql FROM sqlite_master
WHERE type = 'index' AND tbl_name = 'users';

-- Save existing triggers
SELECT sql FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'users';

-- Do the migration...

-- Recreate indexes
CREATE INDEX idx_users_email ON users(email);

-- Recreate triggers
CREATE TRIGGER ... ;

Solution 4: Use a Migration Helper

JAVASCRIPT

function recreateTable(db, tableName, newSchema, dataTransform) {
  const tempName = `${tableName}_migration_temp`;

  db.transaction(() => {
    // Create new table
    db.exec(newSchema.replace(tableName, tempName));

    // Copy data with optional transform
    const oldData = db.prepare(`SELECT * FROM ${tableName}`).all();
    const newData = dataTransform ? oldData.map(dataTransform) : oldData;

    // Insert into new table
    // ... insert logic based on new schema

    // Swap tables
    db.exec(`DROP TABLE ${tableName}`);
    db.exec(`ALTER TABLE ${tempName} RENAME TO ${tableName}`);
  })();
}

Preserve Row Order (if needed)

SQL

-- If you need to preserve original order
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT,
  original_rowid INTEGER
);

INSERT INTO users_new (name, original_rowid)
SELECT name, rowid FROM users ORDER BY rowid;

-- Later, if needed, sort by original_rowid

Best Practices

Plan schema upfront to minimize migrations
Use transactions for safety
Backup before major schema changes
Test migrations on copy of production data
Disable foreign keys during migration
Recreate indexes after table swap