MySQL “ERROR 1005 (HY000): Can't create table

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> 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 >= 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!

Answer 1

You should make car_id a primary key in cars.

Answer 2

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.

Answer 3

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.

Answer 4

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;

Answer 5

all, I solved a problem and wanted to share it:

I had this error <> 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.

Answer 6

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