title: Create table fails with Foreign Key Constraint is incorrectly Formed
body: <h2>Topic</h2>
  
  <p>MariaDB InnoDB Foreign Key Issue</p>
  
  <p>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.</p>
  
  <p>I have a table that is central to a number of tables in my data model.  So something along these lines:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Decided to clean up some DELETE / UPDATE clauses on my FKs quickly this weekend...Famous Last Words...</p>
  
  <p>A related table example is here</p>
  
  <pre><code>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;
  </code></pre>
  
  <h2>Problem</h2>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Now when I attempt to re-create the USERS table I receive this error:</p>
  
  <pre><code>ERROR 1005 (HY000): Can't create table `sprintdb`.`system_users` (errno: 150 "Foreign key constraint is incorrectly formed")
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>