If you’re getting an error that reads something like “ERROR 1050 (42S01): Table ‘customers’ already exists” when trying to create a table in MySQL, it’s probably because there’s already a table in the database with the same name.

To fix this issue, either change the name of the table you’re trying to create, or check the existing table to see if it’s the one you actually need.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

ERROR 1050 (42S01): Table 'customers' already exists

In this case, I’m trying to create a table called Customers, but it already exists in the database.

Solution 1

The most obvious solution is to change the name of the table we’re creating:

CREATE TABLE Customers2 (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected (0.02 sec)

Here, I simply renamed the table to Customers2. In practice, we would probably give it a more appropriate name.

We should also be mindful that if there’s already a table with the same name as the one we’re trying to create, there’s a possibility that our desired table has already been created. In this case we wouldn’t need to recreate it (unless we had good reason). We could either just go ahead and use it, or we could alter it to suit our new requirements.

Solution 2

Another way to deal with this error is to suppress it. We can modify our CREATE TABLE statement to only create the table if it doesn’t already exist:

CREATE TABLE IF NOT EXISTS Customers (
  CustomerId int NOT NULL PRIMARY KEY,
  CustomerName varchar(60) NOT NULL
  );

Result:

Query OK, 0 rows affected, 1 warning (0.00 sec)

In this case, we got a warning. Let’s check the warning:

SHOW WARNINGS;

Result:

+-------+------+----------------------------------+
| Level | Code | Message                          |
+-------+------+----------------------------------+
| Note  | 1050 | Table 'Customers' already exists |
+-------+------+----------------------------------+
1 row in set (0.00 sec)

The warning explicitly tells us that the table already exists.

The Table REALLY Doesn’t Exist?

If you believe that the table really doesn’t exist, perhaps there’s something else going on. See this article on Stack Overflow for a discussion on possible solutions.