mysql - 正确指定外键约束时，其形成不正确

Question

所以我正在创建一个数据库系统，但由于某种原因我收到此错误:

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

我到处都找过了，但还是不明白为什么!

这是我的代码:

# 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

“type”是MySQL的保留字，你尝试使用它作为列名，我建议你使用不同的名称。