MySQL ERROR 1005 HY000 Can t create table foo sql-12c 4 errno 150

Question

I was working on creating some tables in database foo  but every time I end up with errno 150 regarding the foreign key  Firstly  here s my code for creating tables   CREATE TABLE Clients   client id                CHAR 10   NOT NULL   client name              CHAR 50   NOT NULL   provisional license num  CHAR 50   NOT NULL   client address           CHAR 50   NULL   client city              CHAR 50   NULL   client county            CHAR 50   NULL   client zip               CHAR 10   NULL   client phone             INT       NULL   client email             CHAR 255  NULL   client dob               DATETIME  NULL   test attempts            INT       NULL    CREATE TABLE Applications   application id   CHAR 10  NOT NULL   office id        INT      NOT NULL   client id        CHAR 10  NOT NULL   instructor id    CHAR 10  NOT NULL   car id           CHAR 10  NOT NULL   application date DATETIME NULL     CREATE TABLE Instructors   instructor id      CHAR 10   NOT NULL   office id          INT       NOT NULL   instructor name    CHAR 50   NOT NULL   instructor address CHAR 50   NULL   instructor city    CHAR 50   NULL   instructor county  CHAR 50   NULL   instructor zip     CHAR 10   NULL   instructor phone   INT       NULL   instructor email   CHAR 255  NULL   instructor dob     DATETIME  NULL   lessons given      INT       NULL     CREATE TABLE Cars   car id             CHAR 10  NOT NULL   office id          INT      NOT NULL   engine serial num  CHAR 10  NULL   registration num   CHAR 10  NULL   car make           CHAR 50  NULL   car model          CHAR 50  NULL     CREATE TABLE Offices   office id       INT       NOT NULL   office address  CHAR 50   NULL   office city     CHAR 50   NULL   office County   CHAR 50   NULL   office zip      CHAR 10   NULL   office phone    INT       NULL   office email    CHAR 255  NULL     CREATE TABLE Lessons   lesson num     INT            NOT NULL   client id      CHAR 10        NOT NULL   date           DATETIME       NOT NULL   time           DATETIME       NOT NULL   milegage used  DECIMAL 5  2   NULL   progress       CHAR 50        NULL     CREATE TABLE DrivingTests   test num     INT       NOT NULL   client id    CHAR 10   NOT NULL   test date    DATETIME  NOT NULL   seat num     INT       NOT NULL   score        INT       NULL   test notes   CHAR 255  NULL      ALTER TABLE Clients ADD PRIMARY KEY  client id   ALTER TABLE Applications ADD PRIMARY KEY  application id   ALTER TABLE Instructors ADD PRIMARY KEY  instructor id   ALTER TABLE Offices ADD PRIMARY KEY  office id   ALTER TABLE Lessons ADD PRIMARY KEY  lesson num   ALTER TABLE DrivingTests ADD PRIMARY KEY  test num   ALTER TABLE Applications ADD CONSTRAINT FK Applications Offices FOREIGN KEY  office id  REFERENCES Offices  office id   ALTER TABLE Applications ADD CONSTRAINT FK Applications Clients FOREIGN KEY  client id  REFERENCES Clients  client id   ALTER TABLE Applications ADD CONSTRAINT FK Applications Instructors FOREIGN KEY  instructor id  REFERENCES Instructors  instructor id   ALTER TABLE Applications ADD CONSTRAINT FK Applications Cars FOREIGN KEY  car id  REFERENCES Cars  car id   ALTER TABLE Lessons ADD CONSTRAINT FK Lessons Clients FOREIGN KEY  client id  REFERENCES Clients  client id   ALTER TABLE Cars ADD CONSTRAINT FK Cars Offices FOREIGN KEY  office id  REFERENCES Offices  office id   ALTER TABLE Clients ADD CONSTRAINT FK DrivingTests Clients FOREIGN KEY  client id  REFERENCES Clients  client id     These are the errors that I get   mysql gt  ALTER TABLE Applications ADD CONSTRAINT FK Applications Cars FOREIGN KEY  car id  REFERENCES Cars  car id   ERROR 1005  HY000   Can t create table  foo  sql-12c 4   errno  150    I ran SHOW ENGINE INNODB STATUS which gives a more detailed error description   ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100509 20 59 49 Error in foreign key constraint of table foo  sql-12c 4   FOREIGN KEY  car id  REFERENCES Cars  car id   Cannot find an index in the referenced table where the referenced columns appear as the first columns  or column types 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  See http   dev mysql com doc refman 5 1 en innodb-foreign-key-constraints html for correct foreign key definition  ------------   I searched around on StackOverflow and elsewhere online - came across a helpful blog post here with pointers on how to resolve this error - but I can t figure out what s going wrong  Any help would be appreciated

User · Answer

The referenced column must be an index of a single column or the first column in multi column index, and the same type and the same collation.

My two tables have the different collations. It can be shown by issuing show table status like table_name and collation can be changed by issuing alter table table_name convert to character set utf8.

User · Answer

I also received this error  for several tables  along with constraint errors and MySQL connecting and disconnecting when attempting to import an entire database   800 MB   My issue was the result of The MySQL server max allowed packets being too low  To resolve this  on a Mac     Opened  private etc my conf Under   The MySQL server  changed max allowed packet from 1M to 4M  You may need to experiment with this value   Restarted MySQL   The database imported successfully after that   Note I am running MySQL 5 5 12 for Mac OS X  x86 64 bit

User · Answer

Subtle  but this error got me because I forgot to declare a smallint column as unsigned to match the referenced  existing table which was  smallint unsigned   Having one unsigned and one not unsigned caused MySQL to prevent the foreign key from being created on the new table   id smallint 3  not null   does not match  for the sake of foreign keys   id smallint 3  unsigned not null

User · Answer

Also both the tables need to have same character set    for e g    CREATE TABLE1     FIELD1 VARCHAR 100  NOT NULL PRIMARY KEY    FIELD2 VARCHAR 100  NOT NULL  ENGINE INNODB CHARACTER SET utf8 COLLATE utf8 bin    to   CREATE TABLE2     Field3 varchar 64  NOT NULL PRIMARY KEY    Field4 varchar 64  NOT NULL    CONSTRAINT FORIGEN KEY  Field3  REFERENCES TABLE1 FIELD1    ENGINE InnoDB    Will fail because they have different charsets  This is another subtle failure where mysql returns same error

User · Answer

all  I solved a problem and wanted to share it   I had this error  lt   The issue was in that in my statement       alter table system registro de modificacion add foreign key    usuariomodificador id  REFERENCES Usuario id  On delete restrict    I had incorrectly written the CASING  it works in Windows WAMP  but in Linux MySQL it is more strict with the CASING  so writting  Usuario  instead of  usuario   exact casing   generated the error  and was corrected simply changing the casing

User · Answer

You should make car id a primary key in cars

User · Answer

I use Ubuntu linux  and in my case the error was caused by incorrect statement syntax  which I found out by typing perror 150 at the terminal  which gives     MySQL error code 150  Foreign key constraint is incorrectly formed   Changing the syntax of the query from  alter table scale add constraint foreign key  year id  references year id    to  alter table scale add constraint foreign key  year id  references year id     fixed it

User · Answer

The referenced field must be a  Key  in the referenced table  not necessarily a primary key  So the  car id  should either be a primary key or be defined with NOT NULL and UNIQUE constraints in the  Cars  table   And moreover  both fields must be of the same type and collation

User · Answer

Check that BOTH tables have the same ENGINE  For example if you have   CREATE Table FOO       and   CREATE Table BAR    ENGINE INNODB    If you try to create a constraint from table BAR to table FOO  it will not work on certain MySQL versions   Fix the issue by following   CREATE Table FOO    ENGINE INNODB

User · Answer

I got this completely worthless and uninformative error when I tried to   ALTER TABLE  comments  ADD CONSTRAINT FOREIGN KEY   user id   REFERENCES  users    id   ON DELETE SET NULL ON UPDATE CASCADE    My problem was in my comments table  user id was defined as    user id  int 10  unsigned NOT NULL   So    in my case  the problem was with the conflict between NOT NULL  and ON DELETE SET NULL

User · Answer

check to make the field you are referencing to is an exact match with foreign key  in my case one was unsigned and the other was signed so i just changed them to match and this worked  ALTER TABLE customer information ADD CONSTRAINT fk customer information1 FOREIGN KEY  user id  REFERENCES users id  ON DELETE CASCADE ON UPDATE CASCADE

User · Answer

Note  I had the same problem  and it was because the referenced field was in a different collation in the 2 different tables  they had exact same type    Make sure all your referenced fields have the same type AND the same collation

User · Answer

Solved   Check to make sure Primary Key and Foreign Key are exact match with data types  If one is signed another one unsigned  it will be failed  Good practice is to make sure both are unsigned int

User · Answer

I was using a duplicate Foreign Key Name   Renaming the FK name solved my problem   Clarification   Both tables had a constraint called PK1  FK1  etc  Renaming them making the names unique solved the problem

[mysql] MySQL "ERROR 1005 (HY000): Can't create table 'foo.#sql-12c_4' (errno: 150)"

Examples related to mysql

Examples related to foreign-keys

Examples related to mysql-error-1005