MySQL Foreign Key Error 1005 errno 150

Question

I'm doing a small DataBase with MySQL Workbench. I have a main table, called "Immobili", which has a Primary Key composed by four columns: (Comune, Via, Civico, Immobile).

Now, I also have three other tables, wich have the same primary key (Comune, Via, Civico, Immobile), but these fields are also referenced to the table Immobili.

First question: Can I make a Primary Key that is also a Foreign Key?

Second Question: When I try to export the changes it says: Executing SQL script in server

# ERROR: Error 1005: Can't create table 'dbimmobili.condoni' (errno: 150)

CREATE  TABLE IF NOT EXISTS `dbimmobili`.`Condoni` (

  `ComuneImmobile` VARCHAR(50) NOT NULL ,
  `ViaImmobile` VARCHAR(50) NOT NULL ,
  `CivicoImmobile` VARCHAR(5) NOT NULL ,
  `InternoImmobile` VARCHAR(3) NOT NULL ,
  `ProtocolloNumero` VARCHAR(15) NULL ,
  `DataRichiestaSanatoria` DATE NULL ,
  `DataSanatoria` DATE NULL ,
  `SullePartiEsclusive` TINYINT(1) NULL ,
  `SullePartiComuni` TINYINT(1) NULL ,
  `OblazioneInEuro` DOUBLE NULL ,
  `TecnicoOblazione` VARCHAR(45) NULL ,
  `TelefonoTecnico` VARCHAR(15) NULL ,
  INDEX `ComuneImmobile` (`ComuneImmobile` ASC) ,
  INDEX `ViaImmobile` (`ViaImmobile` ASC) ,
  INDEX `CivicoImmobile` (`CivicoImmobile` ASC) ,
  INDEX `InternoImmobile` (`InternoImmobile` ASC) ,

  PRIMARY KEY (`ComuneImmobile`, `ViaImmobile`, `CivicoImmobile`, `InternoImmobile`) ,

  CONSTRAINT `ComuneImmobile`
    FOREIGN KEY (`ComuneImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ComuneImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `ViaImmobile`
    FOREIGN KEY (`ViaImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`ViaImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `CivicoImmobile`
    FOREIGN KEY (`CivicoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`CivicoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  CONSTRAINT `InternoImmobile`
    FOREIGN KEY (`InternoImmobile` )
    REFERENCES `dbimmobili`.`Immobile` (`InternoImmobile` )
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = InnoDB

Showing the Engine Status:

Error in foreign key constraint of table dbimmobili/valutazionimercato:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or columns typse in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables.

Where I'm doing wrong?

Answer 1

For me, I was trying to match up a regular indexed field in a child table, to a primary key in the parent table, and by default some MySQL frontend GUIs like Sequel Pro set the primary key as unsigned, so you have to make sure that the child table field is unsigned too (unless these fields might contain negative ints, then make sure they're both signed).

Answer 2

If your key is a CHAR/VARCHAR or something of that type, another possible problem is different collation. Check if the charset is the same.

Answer 3

In my case the error was due to the referencing table is MyISAM where as referring table was InnoDB.

Converted table engine from MyISAM to InnoDB solves the problem for me.

ALTER TABLE table_name ENGINE=InnoDB;

Answer 4

It's not your specific case, but it's worth noting for anybody else that this error can occur if you try to reference some fields in a table that are not the whole primary key of that table. Obviously this is not allowed.

Answer 5

When a there are 2 columns for primary keys they make up a composite primary key therefore you have to make sure that in the table that is being referenced there are also 2 columns of the same data type.

Answer 6

Give attention to CHARSET and COLLATE parameters when you create a table. In terms of FOREIGN KEY problems something like that:

CREATE TABLE yourTableName (
....
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In my case i couldn´t create the table with FOREIGN KEY references. First i got the Error Code 1005 which pretty much says nothing. Then i added COLLATE and finally the error message complaining about CHARSET.

Error Code: 1253. COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

After that correction my issue was solved.