title: InnoDB foreign key definition cannot resolve column name
body: <p>I have a problem creating a foreign key constraint between two tables.</p>
  
  <p>Here is the first table:</p>
  
  <pre><code>CREATE TABLE `agews_rifiuti_cer` (
    `id_cer` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `agews_id` int(10) unsigned DEFAULT '0',
    `livello` tinyint(4) DEFAULT '1',
    `codice` varchar(10) DEFAULT NULL,
    `descrizione` varchar(255) DEFAULT NULL,
    `note` text,
    `flag_pericoloso` tinyint(1) DEFAULT '0',
    `id_cliente` int(10) unsigned DEFAULT '1',
    `flag_modificato` char(1) DEFAULT 'N',
    PRIMARY KEY (`id_cer`),
    KEY `fk_id_cliente_agews_sgs_codici_cer` (`id_cliente`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>And here is the second one:</p>
  
  <pre><code>CREATE TABLE `lin_98_47_rifiuti` (
    `id_rifiuto` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_azienda` int(10) unsigned NOT NULL DEFAULT '1',
    `id_sede` int(10) unsigned NOT NULL DEFAULT '1',
    `revisione_documento` int(10) unsigned NOT NULL DEFAULT '0',
    `rifiuto` varchar(255) DEFAULT NULL,
    `codice_cer` varchar(10) DEFAULT NULL,
    `nome_interno` varchar(255) DEFAULT NULL,
    `descrizione` text,
    `materie_prime` text,
    `contenitore` text,
    `deposito` text,
    `data_ultima_analisi` date DEFAULT NULL,
    `stato_fisico` enum('Solido pulverulento','Solido non pulverulento','Fangoso palabile','Liquido') DEFAULT 'Solido non pulverulento',
    `quantita` float(9,1) DEFAULT '0.0',
    `unita_misura` enum('Kg','l','mc') DEFAULT 'Kg',
    `id_pericolo` int(10) unsigned DEFAULT NULL,
    `destino` enum('Recupero','Smaltimento') DEFAULT NULL,
    `id_recupero` int(10) unsigned DEFAULT NULL,
    `id_smaltimento` int(10) unsigned DEFAULT NULL,
    `id_cer` int(10) unsigned DEFAULT NULL,
    `immagine` varchar(255) DEFAULT NULL,
    `image_type` varchar(20) DEFAULT NULL,
    `image_content` mediumblob,
    `image_size_x` smallint(5) unsigned DEFAULT '0',
    `image_size_y` smallint(5) unsigned DEFAULT '0',
    `flag_storico` tinyint(1) DEFAULT '0',
    `id_responsabile` int(10) unsigned DEFAULT '0',
    `nome_responsabile` varchar(255) DEFAULT '0',
    `id_ultima_modifica` int(10) unsigned DEFAULT '0',
    `create_log` tinyint(1) DEFAULT '1',
    PRIMARY KEY (`id_rifiuto`,`id_azienda`,`id_sede`,`revisione_documento`),
    KEY `fk_main_lin_98_47_rifiuti` (`id_azienda`,`id_sede`,`revisione_documento`),
    KEY `fk_id_responsabile_lin_98_47_rifiuti` (`id_responsabile`,`id_azienda`,`id_sede`,`revisione_documento`,`nome_responsabile`),
    KEY `fk_id_pericolo_lin_98_47_rifiuti` (`id_pericolo`),
    KEY `fk_id_recupero_lin_98_47_rifiuti` (`id_recupero`),
    KEY `fk_id_smaltimento_lin_98_47_rifiuti` (`id_smaltimento`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>The problem happens the I try to do this:</p>
  
  <pre><code>ALTER TABLE lin_98_47_rifiuti ADD CONSTRAINT fk_id_cer_lin_98_47_rifiuti FOREIGN KEY (id_cer) REFERENCES agews_rifiuti_cer(id_cer) ON UPDATE CASCADE ON DELETE CASCADE;
  </code></pre>
  
  <p>And I get this error:</p>
  
  <pre><code>#1005 - Can't create table 'db_626suite.#sql-71c_13d5' (errno: 150)
  </code></pre>
  
  <p>While the command SHOW INNODB STATUS says:</p>
  
  <pre><code>Error in foreign key constraint of table db_626suite/#sql-71c_13d5:
   FOREIGN KEY (id_cer) REFERENCES agews_rifiuti_cer(id_cer) ON UPDATE CASCADE ON DELETE CASCADE:
  Cannot resolve column name close to:
  ) ON UPDATE CASCADE ON DELETE CASCADE
  </code></pre>
  
  <p>But the syntax and fields definitions seem correct to me. What am I doing wrong?</p>
  
  <p>EDIT:</p>
  
  <p><strong>Marc B</strong> suggested that it could be due to the fact that <code>id_cer</code> is defined as <code>NOT NULL</code> in <code>agews_rifiuti_cer</code>, but I do not think this is the case, in fact please consider this other table:</p>
  
  <pre><code>CREATE TABLE `agews_rifiuti_pericolo` (
    `id_pericolo` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `agews_id` int(10) unsigned DEFAULT '0',
    `codice` varchar(255) DEFAULT NULL,
    `pericolo` varchar(255) DEFAULT NULL,
    `note` text,
    `id_cliente` int(10) unsigned DEFAULT '1',
    `flag_modificato` char(1) DEFAULT 'N',
    PRIMARY KEY (`id_pericolo`),
    KEY `codice_rifiuti_recupero` (`codice`),
    KEY `fk_id_cliente_rifiuti_pericolo` (`id_cliente`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Here <code>id_pericolo</code> is defined as <code>NOT NULL</code> as <code>id_cer</code> above, and yet this works perfectly:</p>
  
  <pre><code>ALTER TABLE lin_98_47_rifiuti ADD CONSTRAINT fk_id_pericolo_lin_98_47_rifiuti FOREIGN KEY (id_pericolo) REFERENCES agews_rifiuti_pericolo(id_pericolo) ON UPDATE CASCADE ON DELETE SET NULL;
  </code></pre>
  
  <p>even if in <code>lin_98_47_rifiuti</code> the field <code>id_pericolo</code> is defined as <code>DEFAULT NULL</code></p>
  
  <p>EDIT 2:</p>
  
  <p>I just tried this minimal setup:</p>
  
  <pre><code>CREATE TABLE `cer` (
    `id_cer` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `codice` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id_cer`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  CREATE TABLE `rifiuti` (
    `id_rifiuto` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `id_cer` int(10) unsigned NULL DEFAULT NULL,
    `rifiuto` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id_rifiuto`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  
  ALTER TABLE rifiuti ADD FOREIGN KEY (id_cer) REFERENCES cer(id_cer) ON UPDATE CASCADE ON DELETE CASCADE;
  </code></pre>
  
  <p>and it all worked correctly, yet I do not understand what the problem might be in my original query.</p>
  
  <p>Also I think this proves that is not a matter of defining the fields as <code>NOT NULL</code> or <code>DEFAULT NULL</code>.</p>