订购表有什么问题？

Question

DROP DATABASE IF EXISTS kp;
CREATE DATABASE kp;

USE kp;

CREATE TABLE pens
(
    pen_id                  INT                       NOT NULL    AUTO_INCREMENT,
    pen_type                VARCHAR(30)               NOT NULL,
    pen_pricing             INT                       NOT NULL,
    construction_date       DATE,
    CONSTRAINT pen_id_pk
    PRIMARY KEY (pen_id)
);


CREATE TABLE customer_information
(
    customer_id              INT          NOT NULL     AUTO_INCREMENT,
    customer_first_name      VARCHAR(30),
    customer_last_name       VARCHAR(30),
    customer_address         VARCHAR(50)  NOT NULL,
    customer_city            VARCHAR(50),
    customer_state           VARCHAR(30),
    customer_country         VARCHAR(40),
    customer_zipcode         VARCHAR(20),
    customer_telephone       VARCHAR(13),
    CONSTRAINT customer_information_pk 
    PRIMARY KEY (customer_id)
);


CREATE TABLE discounts
(
    discount_id              INT           NOT NULL, 
    pen_discount             DECIMAL(5,2),
    discount_description     VARCHAR(50),
    CONSTRAINT discount_id_pk
    PRIMARY KEY (discount_id)
);


CREATE TABLE orders
(
    order_id                  INT          NOT NULL     AUTO_INCREMENT,
    customers_id              INT          NOT NULL,
    pens_id                   INT          NOT NULL,
    customers_address         VARCHAR(50)  NOT NULL,
    initial_prices            INT          NOT NULL,
    pen_discounts             DECIMAL(5,2),
    CONSTRAINT orders_pk
    PRIMARY KEY (order_id),
    CONSTRAINT orders_customer_id
        FOREIGN KEY (customers_id)
        REFERENCES customer_information (customer_id),
    CONSTRAINT orders_pen_id
        FOREIGN KEY (pens_id)
        REFERENCES pens (pen_id),
    CONSTRAINT orders_customer_address
        FOREIGN KEY (customers_address)
        REFERENCES customer_information (customer_address),
    CONSTRAINT orders_initial_price
        FOREIGN KEY (initial_prices)
        REFERENCES pens (pen_pricing),
    CONSTRAINT orders_pen_discounts
        FOREIGN KEY (pen_discounts)
        REFERENCES discounts (pen_discount)
);


CREATE TABLE shipping
(
    shipping_label          INT          auto_increment,
    order_id                INT          NOT NULL,
    customer_id             INT          NOT NULL,
    customer_address        VARCHAR(50)  NOT NULL,
    shipping_cost           INT          NOT NULL        DEFAULT 2.00,
    CONSTRAINT shipping_pk
    PRIMARY KEY (shipping_label),
    CONSTRAINT shipping_order_id
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),
    CONSTRAINT shipping_customer_id
        FOREIGN KEY (customer_id)
        REFERENCES orders (customer_id),
    CONSTRAINT shipping_customer_address
        FOREIGN KEY (customer_address)
        REFERENCES orders (customer_address)
);


INSERT INTO pens VALUES
(DEFAULT, "Fountain", "4.00", "2021-4-6"), 
(DEFAULT, "Ballpoint", "2.00", "2021-4-6"), 
(DEFAULT, "Calligraphy", "6.00", "2021-4-5"),
(DEFAULT, "Gel", "2.00", "2021-4-6"),
(DEFAULT, "Rollerball", "3.00", "2021-4-6"),
(DEFAULT, "Marker", "3.00", "2021-4-6"),
(DEFAULT, "Technical", "5.00", "2021-4-5");


INSERT INTO customer_information VALUES
(DEFAULT, "Joe", "Ryan", "7989 2nd Street", "Chambersburg", "PA", "US", "17201"),
(DEFAULT, "Erin", "Clouser", "8720 South Trout Ave.", "Washington", "PA", "US", "15301"),
(DEFAULT, "Karol", "McCLean", "9050 Littleton St.", "Ozone Park", "NY", "US", "11417"),
(DEFAULT, "Jack", "Ryan", "802 San Juan Rd.", "Pleasanton", "CA", "US", "94566"),
(DEFAULT, "Lenny", "Jones", "8603 Applegate St.", "Moorhead", "MN", "US", "56560"),
(DEFAULT, "Jon", "Holmes", "8540 Lakeshore Street", "Cantonment", "FL", "US", "32533"),
(DEFAULT, "George", "Flavin", "253 Theatre Rd.", "Jacksonville", "NC", "US", "28540"),
(DEFAULT, "Moly", "Beaver", "12 Mammoth Ave.", "Charlottesville", "VA", "US", "22901"),
(DEFAULT, "Harry", "Pope", "7646 Clinton Ave", "Covington", "GA", "US", "30014"),
(DEAFAULT, "Jones", "Terry", "113 St Andrews Lane", "CWNDARE", "", "UK", "CF44 1TE");


INSERT INTO discounts VALUES
("1", "10%", "Out of the Country"),
("1", "15%", "Social Media Post");


INSERT INTO orders VALUES
(DEFAULT, "3", "2", "9050 Littleton St.", "2.00", NULL),
(DEFAULT, "6", "4", "8540 Lakeshore Street", "2.00", NULL),
(DEFAULT, "10", "7", "113 St Andrews Lane", "5.00", "10%"),
(DEFAULT, "3", "1", "9050 Littleton St.", "4.00", NULL),
(DEFAULT, "2", "6", "8720 South Trout Ave.", "3.00", NULL),
(DEFAULT, "4", "4", "802 San Juan Rd.", "2.00", NULL),
(DEFAULT, "1", "5", "7989 2nd Street", "3.00", NULL),
(DEFAULT, "3", "5", "9050 Littleton St.", "3.00", NULL),
(DEFAULT, "7", "3", "253 Theatre Rd.", "6.00", NULL),
(DEFAULT, "3", "4", "9050 Littleton St.", "2.00", NULL);


INSERT INTO shipping VALUES
(DEFAULT, "1", "3", "3", DEFAULT),
(DEFAULT, "2", "6", "6", DEFAULT),
(DEFAULT, "3", "10", "10", DEFAULT),
(DEFAULT, "4", "3", "3", DEFAULT),
(DEFAULT, "5", "2", "2", DEFAULT),
(DEFAULT, "6", "4", "4", DEFAULT),
(DEFAULT, "7", "1", "1", DEFAULT),
(DEFAULT, "8", "3", "3", DEFAULT),
(DEFAULT, "9", "7", "7", DEFAULT),
(DEFAULT, "10", "3", "3", DEFAULT)

Answer 1

订购表有什么问题？

逐个执行查询并读取错误消息。

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8aad085d41ee0992f33e62e8f865b173

成功地执行了前3个查询。第四种产生错误

添加外键约束失败。引用表“orders_customer_address”中缺少约束“customer_information”的索引

也就是说，您必须为FK表达式customer_information (customer_address)创建一个索引。

在添加索引之后，我们会看到引用到另一个索引的相同错误。

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9976aae257a849cd5e25e2d7df5955e7

修正后我们看到第三个类似的错误..。然后第四..。

在试图修复第四个错误之后，我们在表orders中看到了下一个错误消息：

表中不存在键列'customer_id‘

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=65ed6ae2acf23227e226bd27d9e8a24f

这可能是由于行中的shipping错误造成的。

CONSTRAINT shipping_customer_id
    FOREIGN KEY (customer_id)
    REFERENCES orders (customer_id),

因为order不包含具有指定名称的列，但包含列customers_id。

任何情况-通过显示的方式调试您的代码，直到所有问题都解决为止。