MySQL Foreign Key Error 1005 errno 150 primary key as foreign key

Question

I'm making 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).

I also have three other tables, which 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 am I doing wrong?

Haynor

Answer 1

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.

Jellybean

Keep the order for index creating in mind, sometimes you have to correct it. – Glaser

one problem i found, if you don't give field name in referencing table it cause error. like FOREIGN KEY (a_id) REFERENCES tbl_a; even field available in both table with same name. – Denominator

to complete the comment of CShulz. that you ve to take care about index on multi field... you've to check that indexes definition between referencing and referenced tables are defined with same order. If tab_a (referencing table) with a_id and b_id is constraint with tab_b (referenced table) with aa_id and b b_id .. with constraint bind : a_id - aa_id and b_id - bb_id ... so if the indexes used are defined as tab_a_idx (a_id, b_id) so be sure that tab_b idx is on (aa_id, bb_id) and NOT (bb_id, aa_id) – Kissable

I just had this same error. The problem ended up being that the foreign key I was referencing had datatype "INT unsigned". I'm not sure what that means, but changing it to "INT" worked. – Asp

Had the same problem. It was caused by duplicate FK names. – Appreciate

If your problem was signed vs unsigned, then you didn't have it matching the signed/unsigned setting of the referenced column. The columns must match by datatype and unsigned setting. – Odyssey

Answer 2

Double check that the foreign keys have exactly the same type as the field you've got in this table. For example, both should be Integer(10), or Varchar (8), even the number of characters.

Mims

yes, it can fail if suppose on one table the key is tinyint and int on the other, even if their lengths are the same – Hardej

signed/unsigned integer matters too! Just found out thanks to your answer. – Raising

I had an int field referring to a bigint field. I made them both the same and it succeeded. This answer is waay down at the bottom. I wish I had seen it earlier. – Whinchat

Even flags like CAN_BE_NULL, UNSIGNED, etc, any slight difference between the two fields can lead to such an error. Anyway +1 for danp, this is helpful – Plumate

thanks... this solved my problem. I had in the referenced table, customer_id int(20) and in the referencing table I had: foreign key(_customer_id) references customer(customer_id) where _customer_id was defined as _customer_id int(10) – Unsteel

thank you so much it was the length of integer that cause that Error – Shire

Answer 3

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.

Latoyalatoye

Answer 4

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.

Brosy

This was the reason for me. Thought I changed the default latin1_swedish_ci to utf8_unicode_ci, but misclicked utf8mb4_unicode_ci. – Kessinger

Answer 5

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.

Crotchet

Answer 6

I was getting a same error. I found out the solution that I had created the primary key in the main table as BIGINT UNSIGNED and was declaring it as a foreign key in the second table as only BIGINT.

When I declared my foreign key as BIGINT UNSIGED in second table, everything worked fine, even didn't need any indexes to be created.

So it was a datatype mismatch between the primary key and the foreign key :)

Syphilis

Also note that a different Collation between the tables can also cause this even if the data types are the same. – Katydid

Answer 7

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.

Shakiashaking

Answer 8

1. Make sure that both tables are using the same Engine type.
2. Make sure the fields you are indexing have the same type and length.

Tyrannize

Answer 9

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;

Becker

I had the same error, strange MySql did not give an error that gives a hint in that direction. I suggest to start with queries that synchronize the used tables to set the same for Engine (InnoDB), Charset (utf8) and Collate (utf8_general_ci). Always check that the create query using the same CamelCase (lowercase) for all column-names. Check your Primary Keys, your Keys and your Constraints. – Unshackle

Answer 10

I don't have the reputation yet to up vote Steve's suggestion, but it solved my problem.

In my case, I received this error because the two table where created using different database engines--one was Innodb and the other MyISAM.

You can change the database type using : ALTER TABLE t ENGINE = MYISAM;

@see http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html

Percival

Answer 11

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.

Broddy

Answer 12

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.

Poland

Answer 13

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.

Maremma

Answer 14

If anyone has this error with seemingly well formed FK/PK relationships and you used the visual tool, try deleting the offending fk columns and re-adding them in the tool. I was continually getting this error until I redrew the connections which cleared up the issues.

Ashelman

Answer 15

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).

Sanborne

Answer 16

MySQL is notoriously cranky, especially with regards to foreign keys and triggers. I am right now in the process of fine tuning 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.

Kamikamikaze

Answer 17

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.

Adriel