Create table fails with Foreign Key Constraint is incorrectly Formed

Question

Topic

MariaDB InnoDB Foreign Key Issue

Want to start off by saying I'm new to InnoDB and spent all day reading posts yesterday I've tried multiple things along the way to get me where I am now so am I hosed or is there a way out of this dark forest.

I have a table that is central to a number of tables in my data model. So something along these lines:

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;

Decided to clean up some DELETE / UPDATE clauses on my FKs quickly this weekend...Famous Last Words...

A related table example is here

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;

Problem

Modified the FK in the "ATHING" table to include ON DELETE SET NULL. Saved that modification everything seemed ok. I was using HeidiSQL to perform this.

Long story short I was trolling through my list of tables and low and behold my USERS table was GONE! Through a lot of reading and effort I was able to get things cleaned up but felt to really ensure things were good I dropped all FKs pointing at USERS table and dropped the table.

Now 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")

What I noticed post my first attempt at doing this is while I'd thought I'd dropped all FKs there were remnants of keys still out there specifically 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.

Is there a way to move beyond this I feel like there exists some piece of information somewhere whether it be in the file system or in the database itself that needs to be refreshed or removed so that I can move forward...thoughts?

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;