mysql - MySQL 语法中的表键错误

Question

有人可以帮我解决我的错误吗？

-- Create ordered
CREATE TABLE ordered(
orderID TINYINT UNSIGNED AUTO_INCREMENT,
total_cost DECIMAL(10,2),
customerID TINYINT UNSIGNED,
offercode VARCHAR(15),
CONSTRAINT ordered_pk PRIMARY KEY (orderID)
CONSTRAINT ordered_customerID_fk FOREIGN KEY (customerID) REFERENCES customer(customerID) 
    ON UPDATE CASCADE
CONSTRAINT ordered_offercode_fk FOREIGN KEY (offercode) REFERENCES offer(offercode)
    ON UPDATE CASCADE
);


DESCRIBE ordered;



-- Create line item
CREATE TABLE line_item(
item_number TINYINT UNSIGNED,
orderID TINYINT UNSIGNED,
quantity TINYINT,
shipping_amount DECIMAL(6,2)
CONSTRAINT line_item_pk PRIMARY KEY (item_number, orderID)
CONSTRAINT line_item_item_number_fk FOREIGN KEY (item_number) REFERENCES item(item_number)
    ON UPDATE CASCADE,
CONSTRAINT line_item_orderID_fk FOREIGN KEY (orderID) REFERENCES ordered(orderID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

DESCRIBE line_item;

我的 customerID 外键的 ORDERED 表出现错误，而我的 line_item 表的主键出现错误。

谢谢!

Answer 1

您的第一个错误是因为 customer 表不存在。

第二个错误是因为这一行shipping_amount DECIMAL(6,2)，在结尾处添加了一个逗号shipping_amount DECIMAL(6,2),

即使您会遇到主键错误，您也会遇到 item 表错误，因为它不存在。