📋

Description

This error signifies that you are attempting to create or modify a table with a column name that already exists within that table. It typically occurs during `CREATE TABLE` or `ALTER TABLE ADD COLUMN` operations when a new column's name conflicts with an existing one.

💬

Error Message

Duplicate column name '%s'

🔍

Known Causes

3 known causes

⚠️

Redundant Column Definition

You're trying to define a column with a name that has already been used within the same `CREATE TABLE` statement.

⚠️

Adding an Existing Column

An `ALTER TABLE ADD COLUMN` statement attempts to introduce a column using a name that already exists in the target table.

⚠️

Case-Insensitive Conflict

On systems or with collations configured for case-insensitivity, creating a column with a name that differs only in case from an existing one can trigger this error.

🛠️

Solutions

4 solutions available

1. Remove Duplicate Column Definition easy

Each column name must be unique in a table

1

Check your CREATE TABLE for duplicate column names

-- Wrong:
CREATE TABLE users (
  id INT,
  name VARCHAR(100),
  name VARCHAR(200)  -- Duplicate!
);

-- Right:
CREATE TABLE users (
  id INT,
  first_name VARCHAR(100),
  last_name VARCHAR(200)
);

2. Fix ALTER TABLE ADD easy

Column you're adding already exists

1

Check existing columns first

DESCRIBE your_table;

2

If column exists, modify instead of add

-- Instead of ADD:
-- ALTER TABLE users ADD email VARCHAR(255);

-- Use MODIFY:
ALTER TABLE users MODIFY email VARCHAR(500);

3

Or rename the new column

ALTER TABLE users ADD email_new VARCHAR(255);

3. Fix SELECT with Same Column Names easy

Use aliases when selecting same-named columns

1

Alias duplicate column names in CREATE TABLE AS

-- Wrong:
CREATE TABLE combined AS
SELECT u.id, o.id FROM users u JOIN orders o

-- Right:
CREATE TABLE combined AS
SELECT u.id AS user_id, o.id AS order_id FROM users u JOIN orders o

4. Check Migration Scripts medium

Migration may run twice or have duplicates

1

Make ADD COLUMN idempotent

-- Check before adding:
SET @exist := (SELECT COUNT(*) FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table' 
AND COLUMN_NAME = 'new_column');

SET @query := IF(@exist = 0, 
  'ALTER TABLE your_table ADD COLUMN new_column VARCHAR(255)',
  'SELECT 1');

PREPARE stmt FROM @query;
EXECUTE stmt;

🔗

Related Errors

5 related errors

MySQL Error 1060: Duplicate Column Name