Error 156: table already exists when adding a primary key

Question

I'm using the following statement ALTER TABLE my_tbl ADD PRIMARY KEY (id ); to add a primary key to an existing MySQL table. In reply I'm getting the error:

Error 156 : Table 'db_name.my_tbl#1' already exists.

I checked and the table has no duplicate id entries, and if I do something like DROP TABLE my_tbl#1 then the original table (my_tbl) is deleted. It's perhaps interesting to note that my_tbl was created by Create Table my_tbl SELECT id, ... FROM tmp_tbl (where tmp_tbl is a temporary table).

Anyone has an idea what's going on here?

Update: there seems to be some kind of an orphaned table situation here. I tried the suggestions in the answers below, but in my case they did not resolve the problem. I finally used a workaround: I created a table with a different name (e.g. my_tbl_new) , copied the information to this table and added to it the primary key. I Then deleted the original table and renamed the new one back to my_tbl.

Rahul Tripathi · Answer 1 · 2013-08-25T13:10:28.337

1

try something like this:-

ALTER TABLE my_tbl  DROP PRIMARY KEY, ADD PRIMARY KEY(id,id);

or try this:-

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'
    AND TABLE_NAME = '[my_tbl]' 
    AND TABLE_SCHEMA ='dbo' )
BEGIN
    ALTER TABLE [dbo].[my_tbl] ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED  ([ID])
END

or try to flush the table like this:-

DROP TABLE IF EXISTS `my_tbl` ;
   FLUSH TABLES `my_tbl` ; 
   CREATE TABLE `my_tbl` ...

edited Aug 25 '13 at 13:10

answered Aug 25 '13 at 12:51

Rahul Tripathi

168,305
31
280
331

Tried it, but the table doesn't have a primary key. I'm getting the error: Can't DROP 'PRIMARY'; check that column/key exists – hillel Aug 25 '13 at 12:57
@hillel:- Updated my answer with all the possible solutions. Check it!! – Rahul Tripathi Aug 25 '13 at 13:11
@Rahul: same error "Table already exists" when trying the second option. Trying repair gives me "The storage engine for the table doesn't support repair" (this is an innoDB table), also using mysqladmin flush-tables didn't change the error. – hillel Aug 25 '13 at 13:22
@hillel:- Check this out:- http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not – Rahul Tripathi Aug 25 '13 at 13:40
Your SQL to check the constraint existance seems to be for SQL Server, not for MySQL. – Aug 27 '13 at 10:51

score 0 · Answer 2 · answered Aug 25 '13 at 13:04

0

DROP TABLE IF EXISTS `mytable` ;
FLUSH TABLES `mytable` ; 
CREATE TABLE `mytable` ...

Also it might be a permission issue.

answered Aug 25 '13 at 13:04

Mihai

26,325
7
66
81

Tried it but still getting the same output (error 156: table db_name.my_tbl#1 already exists.) – hillel Aug 25 '13 at 13:23
@hillel What engine are you using? – Mihai Aug 25 '13 at 13:24
it's innoDB (I'm using MySQL on Amazon RDS if that matters) – hillel Aug 25 '13 at 13:25
1

@hillel http://stackoverflow.com/questions/3302476/mysql-1050-error-table-already-exists-when-in-fact-it-does-not – Mihai Aug 25 '13 at 13:29

score 0 · Answer 3 · answered Mar 23 '15 at 10:51

I had the same problem while trying to alter indexes, through SQLyog, when my database name contained "-" chars. So I renamed the database to not have them and then it worked just fine.

(Since there's no direct way to rename a DB, I had to copy it to a new one, with correct name)

Error 156: table already exists when adding a primary key

3 Answers3