CODEWITHSUNDEEP
mysqlforeign-keys
Andrea Silvestri
Andrea Silvestri

Reputation: 1134

MySQL. Keep getting Foreign key constraint is incorrectly formed error

I need a hand in understanding why I keep getting this error

Error Code: 1005. Can't create table `test`.`#sql-2394_1043` (errno: 150 "Foreign key constraint is incorrectly formed")

while trying to create a foreign key between the following tables.

CREATE TABLE `componenti_crafting` (
  `id_componente` varchar(255) CHARACTER SET utf8 NOT NULL,
  `nome_componente` varchar(255) CHARACTER SET utf8 NOT NULL,
  `tipo_crafting_componente` set('tecnico','programmazione','chimico') CHARACTER SET utf8 NOT NULL,
  `tipo_componente` set('parametro_x','parametro_y','parametro_z','struttura','batteria','applicativo','supporto','cerotto','fiala','solido','sostanza') CHARACTER SET utf8 NOT NULL,
  `costo_attuale_componente` int(255) DEFAULT NULL,
  `costo_vecchio_componente` int(255) DEFAULT NULL,
  `fcc_componente` int(11) DEFAULT NULL,
  `valore_param_componente` int(11) DEFAULT NULL,
  `volume_componente` int(11) DEFAULT NULL,
  `energia_componente` int(11) DEFAULT NULL,
  `fattore_legame_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `curativo_primario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `psicotropo_primario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `tossico_primario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `curativo_secondario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `psicotropo_secondario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `tossico_secondario_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `possibilita_dipendeza_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `effetto_sicuro_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `descrizione_componente` text CHARACTER SET utf8,
  `tipo_applicativo_componente` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id_componente`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-

CREATE TABLE `componenti_acquistati` (
  `id_acquisto` int(11) NOT NULL AUTO_INCREMENT,
  `cliente_acquisto` int(11) NOT NULL,
  `id_componente_acquisto` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `importo_acquisto` int(11) NOT NULL,
  `data_acquisto` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_acquisto`),
  KEY `fk_acquirente_idx` (`cliente_acquisto`),
  KEY `fk_comp_acq_idx` (`id_componente_acquisto`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Through this command:

ALTER TABLE `componenti_acquistati`
  ADD CONSTRAINT `fk_comp_acq`
FOREIGN KEY (`id_componente_acquisto`)
REFERENCES `componenti_crafting` (`id_componente`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

I have checked the field type, collation and flags (not null, unsigned... etc) but everything between the two fields seems to match. What am I missing?

Thank you in advance!

Upvotes: 0

Views: 263

Answers (1)

Daniele
Daniele

Reputation: 2837

the error is due to the different collation setting for the referencing and the referenced columns. As per the MySQL guide, collations must be the same, see https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

To check the actual types being used, use 

show create table componenti_acquistati;
show create table componenti_crafting ;

To change the collation type on the referenced table, use 

ALTER TABLE `componenti_acquistati`
    CHANGE COLUMN `id_componente_acquisto` `id_componente_acquisto` VARCHAR(255) NOT NULL COLLATE 'utf8_general_ci'

Upvotes: 2

Related Questions