Error Code 1005 Can t create table errno 150

Question

I searched for a solution to this problem on the Internet and checked the Stack nbsp Overflow questions  but none of the solutions worked for my case   I want to create a foreign key from table sira no to metal kod   ALTER TABLE sira no     ADD CONSTRAINT METAL KODU FOREIGN KEY METAL KODU      REFERENCES metal kod METAL KODU      ON DELETE SET NULL     ON UPDATE SET NULL     This script returns   Error Code  1005  Can t create table  ebs  sql-f48 1a3   errno  150    I tried adding an index to the referenced table   CREATE INDEX METAL KODU INDEX ON metal kod  METAL KODU     I checked METAL KODU on both tables  charset and collation   but I couldn t find a solution to this problem  How can I fix this problem    Here is the metal kod table   METAL KODU    varchar 4     NO    PRI DURUM    bit 1     NO METAL ISMI    varchar 30     NO AYAR YOGUNLUK    smallint 6     YES        100

User · Answer

Very often it happens when the foreign key and the reference key don t have the same type or same length

User · Answer

Error Code  1005 -- there is a wrong primary key reference in your code  Usually it s due to a referenced foreign key field that does not exist  It might be you have a typo mistake  or check case it should be same  or there s a field-type mismatch  Foreign key-linked fields must match definitions exactly   Some known causes may be    The two key fields type and or size doesn   t match exactly  For example  if one is INT 10  the key field needs to be INT 10  as well and not INT 11  or TINYINT  You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT 10  and INT 11   You should also check that one is not SIGNED and the other is UNSIGNED  They both need to be exactly the same  One of the key field that you are trying to reference does not have an index and or is not a primary key  If one of the fields in the relationship is not a primary key  you must create an index for that field  The foreign key name is a duplicate of an already existing key  Check that the name of your foreign key is unique within your database  Just add a few random characters to the end of your key name to test for this  One or both of your tables is a MyISAM table  In order to use foreign keys  the tables must both be InnoDB   Actually  if both tables are MyISAM then you won   t get an error message - it just won   t create the key   In Query Browser  you can specify the table type  You have specified a cascade ON DELETE SET NULL  but the relevant key field is set to NOT NULL   You can fix this by either changing your cascade or setting the field to allow NULL values  Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns  You have a default value  that is  default 0  on your foreign key column One of the fields in the relationship is part of a combination  composite  key and does not have its own individual index  Even though the field has an index as part of the composite key  you must create a separate index for only that key field in order to use it in a constraint  You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship The name of your foreign key exceeds the maximum length of 64 characters    For more details  refer to  MySQL Error Number 1005 Can   t create table

User · Answer

In my case  it happened when one table is InnoB and other is MyISAM  Changing engine of one table  through MySQL Workbench  solves for me

User · Answer

MyISAM has been just mentioned   Simply try adding ENGINE MyISAM  DEFAULT CHARSET latin1 AUTO INCREMENT 2   at the end of a statement  assuming that your other tables were created with MyISAM   CREATE TABLE IF NOT EXISTS  tablename       key  bigint 20  NOT NULL AUTO INCREMENT    FOREIGN KEY  key    key   REFERENCES  othertable   id     ENGINE MyISAM  DEFAULT CHARSET latin1 AUTO INCREMENT 2

User · Answer

I had a similar error  The problem had to do with the child and parent table not having the same charset and collation  This can be fixed by appending ENGINE   InnoDB DEFAULT CHARACTER SET   utf8   CREATE TABLE IF NOT EXISTS  country    id  INT 11  NOT NULL AUTO INCREMENT      ENGINE   InnoDB DEFAULT CHARACTER SET   utf8        on the SQL statement means that there is some missing code

User · Answer

I had the very same error message  Finally I figured out I misspelled the name of the table in the command   ALTER TABLE  users  ADD FOREIGN KEY  country id  REFERENCES country  id     versus  ALTER TABLE  users  ADD FOREIGN KEY  country id  REFERENCES countries  id     I wonder why on earth MySQL cannot tell such a table does not exist

User · Answer

Error Code  1005  I had a similar issue  so here are few things that I did try  not in any order  except for the solution        Changed the foreign key names  it didn t work  Reduced the foreign key length Verified the datatypes  darn nothing wrong  Check indexes Check the collations  everything fine  darn again  Truncated the table  of no good use Dropped the table and re-created Tried to see if any circular reference is being created --- all fine Finally  I saw that I had two editors open  One that in PhpStorm  JetBrains  and the other MySQL workbench  It seems that the PhpStorm   MySQL Workbench creates some kind of edit lock   I closed PhpStorm just to check if locking was the case  it could have been the other way around   This solved my problem

User · Answer

check both tables has same schema InnoDB MyISAM  I made them all the same in my case InnoDB and worked

User · Answer

It happened in my case  because the name of the table being referenced in the constraint declaration wasn t correct  I forgot the upper case in the table name    ALTER TABLE  Window  ADD CONSTRAINT  Windows ibfk 1  FOREIGN KEY   WallId   REFERENCES  Wall    id   ON DELETE CASCADE ON UPDATE CASCADE

User · Answer

Sometimes it is due to the master table is dropped  maybe by disabling foreign key checks   but the foreign key CONSTRAINT still exists in other tables  In my case I had dropped the table and tried to recreate it  but it was throwing the same error for me   So try dropping all the foreign key CONSTRAINTs from all the tables if there are any and then update or create the table

User · Answer

My problem was not listed  it was something so silly       The table that has the FK as PK was a composite PK that was declared like this  primary key  CNPJ  CEP  I wanted the CEP field to be FK in another table and I was stuck in this error  the moral of the story just inverted the code above for Primary key  CEP  CNPJ  and it worked  Get tip their friends

User · Answer

The foreign key has to have the exact same type as the primary key that it references  For the example has the type    INT UNSIGNED NOT NULL    the foreing key also have to    INT UNSIGNED NOT NULL     CREATE TABLE employees  id empl INT UNSIGNED NOT NULL AUTO INCREMENT  PRIMARY KEY id     CREATE TABLE offices  id office INT UNSIGNED NOT NULL AUTO INCREMENT  id empl INT UNSIGNED NOT NULL  PRIMARY KEY id   CONSTRAINT  constraint1  FOREIGN KEY   id empl   REFERENCES  employees    id empl   ON DELETE CASCADE ON UPDATE CASCADE   ENGINE InnoDB DEFAULT CHARSET utf8 COMMENT  my offices

User · Answer

This could also happen when exporting your database from one server to another and the tables are listed in alphabetical order by default  So  your first table could have a foreign key of another table that is yet to be created  In such cases  disable foreign key checks and create the database    Just add the following to your script   SET FOREIGN KEY CHECKS 0    and it shall work

[mysql] Error Code: 1005. Can't create table '...' (errno: 150)

Examples related to mysql

Examples related to foreign-keys

Examples related to mysql-error-1005