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  quot Immobili quot   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  gt   InnoDB-4 1 12  and such columns in old tables cannot be referenced by such columns in new tables   Where am I doing wrong

User · Answer

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.

User · Answer

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  quot Indexes quot  right next to  quot Columns quot  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  quot INDEX quot   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL Foreign Key Error 1005 errno 150 primary key as foreign key

Examples related to mysql

Examples related to mysql-workbench

Examples related to mysql-error-1005