MySQL Error 1005?

Question

I'm trying to create a database, but am getting a strange error... This is my code

DROP TABLE IF EXISTS `Person`;
DROP TABLE IF EXISTS `Address`;
DROP TABLE IF EXISTS `Email`;
DROP TABLE IF EXISTS `Airport`;
DROP TABLE IF EXISTS `Customer`;

CREATE TABLE Address
(
AddressID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AddressID),
AddressStreet VARCHAR(255),
AddressCity VARCHAR(255),
AddressState VARCHAR(255),
AddressZip VARCHAR(255),
AddressCountry VARCHAR(255)
);


CREATE TABLE Email
(
EmailID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(EmailID),
EmailAddress VARCHAR(255)
);

CREATE TABLE Person
(
PersonID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(PersonID),
PersonCode VARCHAR(255),
PersonLastName VARCHAR(255),
PersonFirstName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Address` (AddressID) REFERENCES Address(AddressID),
PersonPhone VARCHAR(255),
EmailID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Email` (EmailID) REFERENCES Email(EmailID)
);

CREATE TABLE Airport
(
AirportID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AirportID),
AirportCode VARCHAR(255),
AirportName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Airport_to_Address` (AddressID) REFERENCES Address(AddressID),
AirportLatDeg INT NOT NULL,
AirportLatMin INT NOT NULL,
AirportLongDeg INT NOT NULL,
AirportLongMin INT NOT NULL,
AirportPassFacilityFee FLOAT NOT NULL
);

CREATE TABLE Customer
(
CustomerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(CustomerID),
CustomerCode VARCHAR(255),
CustomerType VARCHAR(255),
PrimaryContact VARCHAR(255),
FOREIGN KEY `fk_Customer_to_Person` (PrimaryContact) REFERENCES Person(PersonID),
CustomerName VARCHAR(255),
CustomerAirlineMiles FLOAT NOT NULL
);

Everything goes great until I get to the Customer table and I try to rename one of the foreign keys. This is the error I'm getting...

Error Code: 1005. Can't create table 'myName.Customer' (errno: 150)

I've tried to look at some other examples of this error but am not understanding the problem. Is it because I'm trying to rename a foreign key in the Customer table? What I want is to get the PersonID from the Person table, but rename it primaryContact in the Customer table. How can I fix this?

Answer 1

When creating a foreign key, the data types should match that of the primary key. Change the PrimaryContact field in the Customer table to match the data type of PersonId, in this case, int:

CREATE TABLE Customer
(
CustomerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(CustomerID),
CustomerCode VARCHAR(255),
CustomerType VARCHAR(255),
PrimaryContact int,
FOREIGN KEY `fk_Customer_to_Person` (PrimaryContact) REFERENCES Person(PersonID),
CustomerName VARCHAR(255),
CustomerAirlineMiles FLOAT NOT NULL
);

The columns must be of the same type as seen in the manual page entitled Using FOREIGN KEY Constraints:

Corresponding columns in the foreign key and the referenced key must have similar data types. 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.