index already exists

This error occurs when you try to create an index that already exists. Use IF NOT EXISTS to safely create indexes.

The index already exists error occurs when creating an index with a name that's already used.

Understanding the Error

Error: index idx_users_email already exists

An index named idx_users_email already exists.

Common Causes

1. Running Index Creation Twice

SQL

CREATE INDEX idx_users_email ON users(email);
-- Run again:
CREATE INDEX idx_users_email ON users(email);
-- Error: index idx_users_email already exists

2. Different Index Same Name

Trying to create a different index with the same name:

SQL

CREATE INDEX my_index ON users(email);
CREATE INDEX my_index ON orders(total);  -- Same name, different table
-- Error!

How to Fix It

Solution 1: Use IF NOT EXISTS

SQL

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- Safe to run multiple times

Solution 2: Check Before Creating

SQL

-- List all indexes
SELECT name FROM sqlite_master WHERE type='index';

JAVASCRIPT

function indexExists(db, name) {
  const result = db.prepare(`
    SELECT name FROM sqlite_master
    WHERE type='index' AND name=?
  `).get(name);
  return !!result;
}

Solution 3: Drop Then Create

SQL

DROP INDEX IF EXISTS idx_users_email;
CREATE INDEX idx_users_email ON users(email);

Solution 4: Use Unique Names

Include table name in index name to avoid conflicts:

SQL

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category_id ON products(category_id);

Viewing Existing Indexes

SQL

-- All indexes
SELECT name, tbl_name, sql FROM sqlite_master WHERE type='index';

-- Indexes for specific table
PRAGMA index_list(users);

-- Index details
PRAGMA index_info(idx_users_email);

Best Practices

Use IF NOT EXISTS for all index creation
Use consistent naming: idx_tablename_columnname
Include in migrations with proper tracking
Document indexes in schema documentation