MySQL Forums
Forum List  »  InnoDB

error in foreign key constraint definition
Posted by: Matteo Tassinari
Date: November 08, 2012 11:00AM

Hi everybody,

I have a problem creating a foreign key constraint between two tables.

Here is the first table:

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;

And here is the second one:

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;

The problem happens the I try to do this:

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;

And I get this error:

#1005 - Can't create table 'db_626suite.#sql-71c_13d5' (errno: 150)

While the command SHOW INNODB STATUS says:

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

But the syntax and fields definitions seem correct to me. What am I doing wrong?

Options: ReplyQuote


Subject
Views
Written By
Posted
error in foreign key constraint definition
2145
November 08, 2012 11:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.