title: Consequences of removing index used for foreign keys in MySQL
body: <p>It appears one can remove the index created for a foreign key in MySQL 5.5, with a small "trick" as shown here:</p>
  
  <pre><code>mysql &gt; create table commands (
    id int primary key auto_increment, name  varchar(255));
  mysql &gt; create table data (
          dim_command int, cnt int NOT NULL, 
          CONSTRAINT FOREIGN KEY (dim_command) references commands(id));
  </code></pre>
  
  <p>Now an index is created, that cannot be dropped:</p>
  
  <pre><code>mysql &gt; show create table data;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                          |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | data  | CREATE TABLE `data` (
    `dim_command` int(11) DEFAULT NULL,
    `cnt` int(11) NOT NULL,
    KEY `dim_command` (`dim_command`),
    CONSTRAINT `data_ibfk_1` FOREIGN KEY (`dim_command`) REFERENCES `commands` (`id`)
  ) ENGINE=InnoDB
  
  mysql &gt; alter table data drop index dim_command;
  ERROR 1553 (HY000): Cannot drop index 'dim_command': needed in a foreign key constraint
  </code></pre>
  
  <p>But it can be tricked into removing:</p>
  
  <pre><code>mysql &gt; set foreign_key_checks=Off;
  Query OK, 0 rows affected (0.00 sec)
  mysql &gt; alter table data drop index dim_command;
  Query OK, 0 rows affected (0.51 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  mysql &gt; set foreign_key_checks=On;
  Query OK, 0 rows affected (0.00 sec)
  </code></pre>
  
  <p>At this point:</p>
  
  <ul>
  <li>The <code>data</code> table still has the foreign key constraint specification (shown by doing e.g. a <code>show create table data</code> )</li>
  <li>But the constraint seems to not be enforced, i.e. one can
     insert rows into the <code>data</code> table that references non-existing rows in the
     <code>commands</code> table.</li>
  </ul>
  
  <p>The question I have is if there are other consequences of removing the index used for a foreign key constraint in this manner on InnoDB tables ?</p>
  
  <p>(The background for this is I have a data warehouse where someone has added foreign keys to the fact table containing hundreds of millions of rows, which references other tables with just a handful of rows - making indexes on such columns useless for query performance, while taking up <em>a lot</em> of disk space and severely impacting insertion performance. The integrity is of less concern and is enforced by the ETL process in the data warehouse - but keeping the foreign key constraint is useful for documentation and 3. party visualization tools)</p>