📋

Description

This error occurs when you attempt to create a new table in a MySQL database, but a table with the specified name already exists within the current database. It prevents the creation of duplicate tables, ensuring schema integrity and avoiding data conflicts.

💬

Error Message

Table '%s' already exists

🔍

Known Causes

3 known causes

⚠️

Duplicate Table Name

You are attempting to create a table using a name that is already in use by another table in the same database or schema.

⚠️

Repeated Script Execution

A SQL script containing `CREATE TABLE` statements has been executed multiple times without first dropping existing tables or checking for their presence.

⚠️

Schema Migration Conflict

During a database migration or deployment, the script attempts to create tables that were already created in a previous step or already exist in the target environment.

🛠️

Solutions

5 solutions available

1. Use IF NOT EXISTS easy

Create table only if it doesn't exist

1

Add IF NOT EXISTS to CREATE TABLE

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

2. Drop and Recreate Table medium

Remove existing table first

1

Drop existing table

DROP TABLE IF EXISTS users;

2

Then create new table

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

3. Backup Before Drop medium

Save data before recreating table

1

Create backup table

CREATE TABLE users_backup AS SELECT * FROM users;

2

Drop and recreate

DROP TABLE users;
CREATE TABLE users (
  -- new structure
);

3

Restore data if needed

INSERT INTO users (col1, col2) SELECT col1, col2 FROM users_backup;

4

Drop backup when confirmed

DROP TABLE users_backup;

4. Use Different Table Name easy

Choose a unique name for the new table

1

Check existing table names

SHOW TABLES LIKE '%users%';

2

Use versioned or prefixed name

CREATE TABLE users_v2 (
  -- new structure
);

5. Modify Existing Table Instead medium

ALTER table instead of recreating

1

Add new columns

ALTER TABLE users ADD COLUMN new_column VARCHAR(255);

2

Modify existing columns

ALTER TABLE users MODIFY COLUMN email VARCHAR(500);

3

Rename table if needed

RENAME TABLE users TO users_old;
CREATE TABLE users (...);

🔗

Related Errors

5 related errors

MySQL Error 1050: Table Already Exists