Create table fails with Foreign Key Constraint is incorrectly Formed

Question

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.

Spinks

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... – Spinks

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? – Spinks

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." – Doro

Answer 1

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;

Bazemore

It's also worth checking the storage engines on the tables match. For instance trying to set a FK between InnoDB and MyISAM will fail – Unskillful

I also experienced the same. I don't know how but my foreign key was set to varchar instead of INT(11) XD – Phial

The flag unsigned causes problems too – Anaglyph

Another thing is you have to check the collations if you use varchar or char keys – Chuffy

Weird. The M in INT(M) has nothing to do with size. It stands for display width. – Loren

Answer 2

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.

Ingeingeberg