将ER图转换为SQL数据表时，如何正确定义外键？

你的SQL代码里外键无法生效，核心问题集中在字段未定义、数据类型不匹配、拼写错误、表创建顺序错误、引用非主键/唯一键这几类，下面直接给出问题说明和修正后的完整代码：

核心规则回顾 #

• 外键字段必须先在当前表中声明，才能添加外键约束
• 外键字段的数据类型必须和被引用的主键/唯一键完全一致
• 字段名拼写必须完全匹配（部分数据库严格区分大小写）
• 被引用的表必须先于引用它的表创建（或使用延迟约束，建议调整创建顺序）
• 外键只能引用目标表的主键或唯一约束字段

修正后的完整SQL代码 #

-- 先创建无外键依赖的基础表
CREATE TABLE Employee (
    employeeID INT NOT NULL,
    employeeName VARCHAR(20) NOT NULL,
    employeeAddress VARCHAR(40) NOT NULL,
    employeeNumber INT NOT NULL,
    hireDate DATE NOT NULL,
    employeePosition VARCHAR(30) NOT NULL,
    employeeStatus VARCHAR(15) NOT NULL,
    PRIMARY KEY (employeeID)
);

CREATE TABLE Memberships (
    membershipID VARCHAR(20) NOT NULL,
    joiningFee FLOAT NOT NULL,
    discount FLOAT NOT NULL,
    PRIMARY KEY (membershipID)
);

CREATE TABLE Vendor (
    vendorID INT NOT NULL,
    companyName VARCHAR(40) NOT NULL,
    companyAddress VARCHAR(40) NOT NULL,
    companyNumber INT NOT NULL,
    faxNumber INT,
    PRIMARY KEY (vendorID)
);

CREATE TABLE Manufacturer (
    manufacturerID INT NOT NULL,
    companyName VARCHAR(40),
    companyAddress VARCHAR(40),
    companyPhone INT NOT NULL,
    faxNumber INT,
    PRIMARY KEY (manufacturerID)
);

CREATE TABLE EquipmentType (
    identificationNumber INT NOT NULL,
    equipmentDescription VARCHAR(40) NOT NULL,
    manufacturerModelNumber INT NOT NULL,
    maintenenceInterval VARCHAR(40),
    PRIMARY KEY (identificationNumber),
    -- 给设备描述加唯一约束，允许被外键引用
    UNIQUE KEY idx_unique_equipment_desc (equipmentDescription)
);

CREATE TABLE Room (
    roomNumber INT NOT NULL,
    classCapacity INT NOT NULL,
    -- 先定义外键关联字段
    equipmentDescription VARCHAR(40),
    PRIMARY KEY (roomNumber),
    FOREIGN KEY (equipmentDescription) REFERENCES EquipmentType(equipmentDescription)
);

CREATE TABLE FitnessCourse (
    courseNumberID INT NOT NULL,
    courseName VARCHAR(30) NOT NULL,
    courseDescription VARCHAR(50),
    courseCategory VARCHAR(30) NOT NULL,
    courseDuration TIME NOT NULL,
    courseFee INT NOT NULL,
    PRIMARY KEY (courseNumberID)
);

CREATE TABLE ExerciseEquipment (
    serialNumber INT NOT NULL,
    purchaseDate DATE NOT NULL,
    lastMaintenance DATE,
    -- 添加关联设备类型的字段
    identificationNumber INT NOT NULL,
    PRIMARY KEY (serialNumber),
    FOREIGN KEY (identificationNumber) REFERENCES EquipmentType(identificationNumber)
);

-- 再创建有外键依赖的表
CREATE TABLE Member (
    memberID INT NOT NULL,
    memberName VARCHAR(20) NOT NULL,
    memberAdress VARCHAR(40) NOT NULL,
    memberPhone INT NOT NULL,
    memberGender VARCHAR(20),
    birthDate DATE NOT NULL,
    dateJoined DATE NOT NULL,
    -- 匹配Memberships表的主键类型
    membershipID VARCHAR(20) NOT NULL,
    PRIMARY KEY (memberID),
    FOREIGN KEY (membershipID) REFERENCES Memberships(membershipID)
);

CREATE TABLE PurchaseTransaction (
    transactionNumber INT NOT NULL,
    transactionDate DATE NOT NULL,
    itemPurchased VARCHAR(40),
    quantity INT NOT NULL,
    pricePerUnit FLOAT NOT NULL,
    overallTotal FLOAT NOT NULL,
    -- 先定义外键字段
    memberID INT NOT NULL,
    employeeID INT NOT NULL,
    PRIMARY KEY (transactionNumber),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (employeeID) REFERENCES Employee(employeeID)
);

CREATE TABLE StoreItem (
    itemID INT NOT NULL,
    itemDescription VARCHAR(40) NOT NULL,
    standardPrice FLOAT NOT NULL,
    quantity INT NOT NULL,
    reorderPoint VARCHAR(40),
    -- 定义外键字段
    vendorID INT NOT NULL,
    PRIMARY KEY (itemID),
    FOREIGN KEY (vendorID) REFERENCES Vendor(vendorID)
);

CREATE TABLE SupplyOrder (
    orderNumber INT NOT NULL,
    datePlaced DATE NOT NULL,
    dateReceived DATE,
    itemOrdered VARCHAR(40) NOT NULL,
    quantityOrdered INT NOT NULL,
    quantityReceived INT,
    costCharged FLOAT NOT NULL,
    totalCost FLOAT NOT NULL,
    -- 定义外键字段
    employeeID INT NOT NULL,
    PRIMARY KEY (orderNumber),
    FOREIGN KEY (employeeID) REFERENCES Employee(employeeID)
);

CREATE TABLE Class (
    referenceNumberID INT NOT NULL,
    employeeID INT,
    startDate DATE NOT NULL,
    daysHeld VARCHAR(30) NOT NULL,
    timePeriod VARCHAR(30) NOT NULL,
    -- 定义外键字段
    roomNumber INT NOT NULL,
    courseNumberID INT NOT NULL,
    PRIMARY KEY (referenceNumberID),
    FOREIGN KEY (employeeID) REFERENCES Employee(employeeID),
    FOREIGN KEY (roomNumber) REFERENCES Room(roomNumber),
    FOREIGN KEY (courseNumberID) REFERENCES FitnessCourse(courseNumberID)
);

CREATE TABLE WorkOrder (
    workOrderID INT NOT NULL,
    orderDate DATE NOT NULL,
    serialNumber INT NOT NULL,
    amountCharged FLOAT NOT NULL,
    -- 定义外键字段
    manufacturerID INT NOT NULL,
    PRIMARY KEY (workOrderID),
    FOREIGN KEY (serialNumber) REFERENCES ExerciseEquipment(serialNumber),
    FOREIGN KEY (manufacturerID) REFERENCES Manufacturer(manufacturerID)
);

关键修正点 #

• Member表：将membershipID类型从INT改为VARCHAR(20)，与Memberships表主键类型匹配，添加外键约束
• PurchaseTransaction/StoreItem/SupplyOrder表：先在表内定义外键字段，再添加约束
• Room/EquipmentType表：修正主键拼写错误，给equipmentDescription加唯一约束以支持外键引用
• ExerciseEquipment/WorkOrder表：修正主键、外键拼写错误，补充缺失的外键关联字段
• 表创建顺序：调整为“先基础表，后依赖表”，避免“表不存在”错误

内容的提问来源于stack exchange，提问作者Brianna