Foreign key issue in mysql (error 1005)

Question

I have attempted to create the following tables:

Customer:

CREATE TABLE Customer(customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
                      surname VARCHAR(15) NOT NULL,
                      forename VARCHAR(15) NOT NULL,
                      DOB TIMESTAMP NOT NULL,
                      phone_no VARCHAR(20) NOT NULL,
                      email_address VARCHAR(30),
                      postcode VARCHAR(15) NOT NULL, 
                      PRIMARY KEY(customer_id)) ENGINE=INNODB;

When I attempt to create the 'Booking' table afterwards, I get an error

"ERROR 1005(HY000): can't create table 'Test.Booking' (errno:150)"

I checked the error online and found out that it was relating to the Foreign key connection that I attempted to make between the tables; which I will give below.

Booking:

CREATE TABLE Booking (booking_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
                      customer_id INT NOT NULL,
                      staying_from TIMESTAMP,
                      staying_until TIMESTAMP,
                      cost INT,
                      PRIMARY KEY(booking_id),
                      FOREIGN KEY(customer_id) REFERENCES Customer(customer_id)
                      ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE=INNODB;

score 2 · Answer 1 · answered Dec 02 '12 at 16:59

As documented under FOREIGN KEY Constraints:

Foreign keys definitions are subject to the following conditions:

[ deletia ]

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

In Customer you have INT UNSIGNED whereas in Booking you have INT.

Foreign key issue in mysql (error 1005)

1 Answers1

Linked