10
create table users (id int not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

create table athing (id int not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int 
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;

I modified the FK in the "ATHING" table to include ON DELETE SET NULL using HeidiSQL.

My USERS table was GONE! I was able to get things cleaned up. I dropped all FKs pointing at USERS and dropped the table.

When I attempt to re-create the USERS table I receive this error:

ERROR 1005 (HY000): Can't create table `sprintdb`.`system_users` (errno: 150 "Foreign key constraint is incorrectly formed")

There are still indexes that supported those keys on some of the tables. In querying the INNODB_SYS_TABLES and INNODB_SYS_INDEXES tables that those indexes that I thought were removed still exist in these system tables.

philipxy
  • 14,867
  • 6
  • 39
  • 83
John
  • 101
  • 1
  • 1
  • 4
  • Yes sorry a number of issues occurred during this process. I literally lost the table was able to get it's structure back without data and to ensure consistency and verify everything was ok I decided to drop all foreign keys from all referencing tables and drop the USERS table. When I try and create the USERS table the result is the error posted above. Backup failed due to the USERS table disappearance caused a view with the name like active_blah which happened to be the first object in the sqldump and failed due to errors... – John Jun 08 '14 at 20:31
  • So the positive thing is this is a primary development environment without a lot of data...unfortunately the dev'rs are chomping at the bit for the environment...so what I'm trying to do is find the shortest path to fix this. I read something indicating that if I rebuilt each of the tables who had FK references to this table that that should correct the issue. Is that fact or fiction and or should I just effectively re-generate the environment from my modeling tool and move data across as I can? – John Jun 08 '14 at 21:41
  • If you have the data model saved or it is relatively easy to recreate, then sure I would bite the bullet and start from scratch instead of likely wasting time on gimmicky tricks. You may even find improvements to the data model. Often times, when I rewrite code it usually gets better. Remind the devr's that "there is never time to do it right, but always time to do it over." – ron tornambe Jun 08 '14 at 22:06

2 Answers2

21

I have received this message many times while using 3rd party tools to create tables and then constrain against existing tables. It's either one of two things:

  • The int columns have different sizes
  • The int columns have different flags (sans AUTO_INCREMENT)

As an example, I created a table with a tool that somehow created a column as INT(10) instead of the expected INT(11). Even though I just chose INT when creating both, it was messed up - never tracked down why.

Long story short, it's generally best to explicitly state the INT size when creating a table.

In your case, the following should work:

create table users (id int(11) not null auto_increment
, username varchar(255) NOT NULL
, password varchar(255) NOT NULL
, active int NOT NULL
, PRIMARY KEY (id))
ENGINE=InnoDB COLLATE=utf8_unicode_ci;

create table athing (id int(11) not null auto_increment
, name varchar(255) not null
, status varchar(255) not null
, created_by_user_id int(11) not null
, PRIMARY KEY (id)
, CONSTRAINT athing_fk1 FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB COLLATE=utf8_unicode_ci;
h0tw1r3
  • 6,618
  • 1
  • 28
  • 34
0

In my case, I received this error when my SQL script for creating the datastructure contained a foreign key that referenced a table that was not yet created. Moving the creating of the referencing table after creating the target table was the solution.

SurfMan
  • 1,685
  • 12
  • 19