MySQL Foreign Key Error 1005 errno 150

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?

 Answers

When creating a foreign key constraint, MySQL requires a usable index on both the referencing table and also on the referenced table. The index on the referencing table is created automatically if one doesn't exist, but the one on the referenced table needs to be created manually (Source). Yours appears to be missing.

Test case:

CREATE TABLE tbl_a (
    id int PRIMARY KEY,
    some_other_id int,
    value int
) ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
ERROR 1005 (HY000): Can't create table 'e.tbl_b' (errno: 150)

But if we add an index on some_other_id:

CREATE INDEX ix_some_id ON tbl_a (some_other_id);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE TABLE tbl_b (
    id int PRIMARY KEY,
    a_id int,
    FOREIGN KEY (a_id) REFERENCES tbl_a (some_other_id)
) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

This is often not an issue in most situations, since the referenced field is often the primary key of the referenced table, and the primary key is indexed automatically.

---

I realize this is an old post, but it ranks high in Google, so I'm adding what I figured out for MY problem. If you have a mix of table types (e.g. MyISAM and InnoDB), you will get this error as well. In this case, InnoDB is the default table type, but one table needed fulltext searching so it was migrated to MyISAM. In this situation, you cannot create a foreign key in the InnoDB table that references the MyISAM table.

---

I had this error and found the reason for the error in my case. I'm still answering to this old post because it ranks pretty high on Google.

The variables of both of the column I wanted to link were integers but one of the ints had 'unsigned' checked on. Simply un-checking that fixed my error.

---

I had the exact same problem, but the solution to my problem was entirely different. I had, somewhere else in the database, a foreign key with the same name. That caused the error 1005.

Renaming my foreign key to something more specific to that situation solved the problem.

---

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;

---

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.

---

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.

---

MySQL is notoriously cranky, especially with regards to foreign keys and triggers. I am right now in the process of fine tunning one such database, and ran into this problem. It is not self evident or intuitive, so here it goes:

Besides checking if the two columns you want to reference in the relationship have the same data type, you must also make sure the column on the table you are referencing is an index. If you are using the MySQL Workbench, select the tab "Indexes" right next to "Columns" and make sure the column referenced by the foreign key is an index. If not, create one, name it something meaningful, and give it the type "INDEX".

A good practice is to clean up the tables involved in relationships to make sure previous attempts did not create indexes you don't want or need.

I hope it helped, some MySQL errors are maddening to track.

---

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

Yes. In fact for MySQL workbench I've gotten in the habit of only using primary keys for foreign keys. Really cuts down on the random errors received, like the err:150 stated in the question.

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

This does have something to with indexes, or more specifically how MySQL workbench interprets and works with them. It really gets confused if you change a lot in the model (e.g. foreign keys, indexes, col orders) all in the same forward engineering operation, especially if there is already a database on the other end. For example, I don't think automatically created indexes where deleted automatically after deleting a foreign key.

Here is what I did to fix the error your receiving. Note, it seems cumbersome but compared to the amount of time I spent using other methods, it's not.

1. Make all foreign keys primary keys in the lookup table (the 1 in the 1 to many).

In my case this involved changing id as the pk to username in tbl_users, to username AND company in tbl_companies, and to username AND company AND contact in tbl_company_contacts. It's an app for multiple users to enter multiple company contacts, allowing overlapping and hidding of other users contacts.

2. Delete all diagram relationships and all table index that are not primary keys.

This fixes most of the index issues that are really caused by a buggy MySQL workbench.

3. If your doing this from start to finish, drop the schema on the server so mysql workbench doesn't get confused about the existing indexs and lack there off in the model (issue is caused bby index and foreign key relationship, not index alone).

This reduces a lot of the decisions that the DB, server and Mysql workbench have to make a great deal. These decisions about how to forward engineer something are complicated and intelligent, but imperfect.

4. Now, I consider this back to square one (generally after designing to much too quickly without a stepped process). I still have all the tables, but they are clean at this stage. Now you just:

First, forward engineer just to make sure the tables (without relationships) work as expected.

Follow the relationship chain down thru the primary keys, starting at the top most table (i'm my case tbl_users to tbl_companies). After each relationship, always forward engineer to make sure it runs, then save the model and close, then reverse engineer the model to make sure it takes. This allows you to quickly isolate the problems as they arise, in my case left over index used by old deleted foreign keys (happened 2-3 times).

And tadda, back where you needed to be.