Foreign key constraint incorrectly formed when it is specified correctly

Question

So i'm creating a database system but for some reason im getting this error:

Static analysis:

3 errors were found during analysis.

A comma or a closing bracket was expected. (near "CHECK" at position 145) Unexpected beginning of statement. (near "consignmentType" at position 156) Unrecognized statement type. (near "IN" at position 172) SQL query:

CREATE TABLE Consignment ( trackingID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, dispatchDate date NOT NULL, type varchar(10) NOT NULL CHECK ( consignmentType IN ('Collection', 'Delivery') ), deliveryAddressID integer NOT NULL, returnAddressID integer NOT NULL, packages integer NOT NULL, FOREIGN KEY (deliveryAddressID)REFERENCES Address(addressID), FOREIGN KEY (returnAddressID)REFERENCES Address(addressID), FOREIGN KEY (packages)REFERENCES PackageIDs(packagesID) ) ENGINE=InnoDB

MySQL said: Documentation

.#1005 - Can't create table courierdb.consignment (errno: 150 "Foreign key constraint is incorrectly formed") (Details…)

I've looked everywhere and i still can't figure out why!

Here is my code:

# Create Category table
CREATE TABLE Category (
    categoryID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    name varchar(255) NOT NULL
) ENGINE=InnoDB;

# Create Package table
CREATE TABLE Package (
    packageID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    price integer NOT NULL, 
    itemName varchar(255) NOT NULL,
    category integer NOT NULL,
    FOREIGN KEY (category)REFERENCES Category(categoryID)
) ENGINE=InnoDB;

# Create DeliveryMethod table
CREATE TABLE DeliveryMethod (
    methodID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    deliveryMethod varchar(7) NOT NULL CHECK (
        deliveryMethod IN ('Bicycle', 'Car', 'Van')
    )
) ENGINE=InnoDB;

# Create Address table
CREATE TABLE Address (
    addressID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    buildingName varchar(255) NOT NULL, 
    streetName varchar(255) NOT NULL, 
    county varchar(255) NOT NULL, 
    postcode varchar(255) NOT NULL
) ENGINE=InnoDB;

/* NORMALIZATION 1 FOR PACKAGES START */
# Create Package table
CREATE TABLE PackageIDs (
    packagesID integer,
    package integer,
    FOREIGN KEY (package)REFERENCES Package(packageID)
) ENGINE=InnoDB;
/* NORMALIZATION END */

# Create Consignment table
CREATE TABLE Consignment (
    trackingID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    dispatchDate date NOT NULL, 
    type varchar(10) NOT NULL CHECK (
        consignmentType IN ('Collection', 'Delivery')
    ), 
    deliveryAddressID integer NOT NULL,
    returnAddressID integer NOT NULL,
    packages integer NOT NULL,
    FOREIGN KEY (deliveryAddressID)REFERENCES Address(addressID),
    FOREIGN KEY (returnAddressID)REFERENCES Address(addressID),
    FOREIGN KEY (packages)REFERENCES PackageIDs(packagesID)
) ENGINE=InnoDB;

/* NORMALIZATION FOR DELIVERY METHOD START */
# Create dMethood table
CREATE TABLE dMethod (
    dMethodID integer,
    dMethods integer,
    FOREIGN KEY (dMethods)REFERENCES DeliveryMethod(methodID)
) ENGINE=InnoDB;
/* NORMALIZATION END */

# Create Branch table
CREATE TABLE Branch (
    branchID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    branchName varchar(255) NOT NULL, 
    headOfficeID integer NOT NULL, 
    managerID integer NOT NULL, 
    deliveryMethods integer NOT NULL,
    address integer NOT NULL,
    FOREIGN KEY (headOfficeID)REFERENCES Branch(branchID),
    /* Cannot add this yet because there is no Employee table! See next queries... */
    FOREIGN KEY (managerID)REFERENCES Employee(staffNo),
    FOREIGN KEY (deliveryMethods)REFERENCES dMethod(dMethodID),
    FOREIGN KEY  (address)REFERENCES Address(addressID)
) ENGINE=InnoDB;

# Create Employee table
CREATE TABLE Employee (
    staffNo integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    NIN varchar(9) NOT NULL unique, 
    firstName varchar(255) NOT NULL, 
    lastName varchar(255) NOT NULL, 
    dateOfBirth date NOT NULL, 
    emailAddress varchar(255) NOT NULL, 
    mobileNo integer NOT NULL, 
    salary integer, 
    branchID integer NOT NULL, 
    supervisorID integer NOT NULL,
    address integer NOT NULL,
    FOREIGN KEY (branchID) REFERENCES Branch(branchID),
    FOREIGN KEY (supervisorID) REFERENCES Employee(staffNo),
    FOREIGN KEY (address) REFERENCES Address(addressID)
) ENGINE=InnoDB;

# Add Branch foreign key constraint for managerID
ALTER TABLE Branch ADD FOREIGN KEY (managerID) REFERENCES Employee(staffNo);

# Create Customer table
CREATE TABLE Customer (
    customerID integer NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    firstName varchar(255) NOT NULL, 
    lastName varchar(255) NOT NULL, 
    dateOfBirth date NOT NULL, 
    emailAddress varchar(255) NOT NULL, 
    mobileNo integer NOT NULL, 
    customerBranchID integer NOT NULL, 
    address integer NOT NULL,
    FOREIGN KEY (customerBranchID) REFERENCES Branch(branchID),
    FOREIGN KEY (address) REFERENCES Address(addressID)
) ENGINE=InnoDB;

Answer 1

try this

type varchar(10) NOT NULL CHECK (
    type IN ('Collection', 'Delivery')
), 

Answer 2

"type" is a reserved word for MySQL, you try to use it for a column name, I suggest you use a different name.