两列主键、自动递增和外键

Question

以下查询成功，但是当我在订单ID中输入空值并为项目ID输入不同的值（列中不存在的值）时，订单ID仍然递增。如何保留相同的订单编号？外键的声明方式有问题吗？

CREATE TABLE orders(
    orderid int not null auto_increment, 
    itemid int not null, 
    quantity int not null, 
    tot_price int not null, 
    cid int not null, 
    code varchar(10), 
    order_time time, 
    order_date date ,
    constraint order_pk primary key (orderid, itemid), 
    foreign key (itemid) references items(itemid), 
    foreign key (cid) references customer(cid), 
    foreign key (code) references coupon(code)
);

Answer 1

问题源于尝试在单个表中建立一对多（顺序到项目）关系。 这将以糟糕的结局结束。 你需要两个。 一个用于订单，一个用于订单中的项目。 无论好坏，这就是关系数据库做列表的方式。

CREATE TABLE orders (
    orderid int not null primary key auto_increment, 
    cid int not null references customer(cid),
    code varchar(10) references coupon(code),
    when_ordered datetime,
    INDEX(when_ordered)
);
CREATE TABLE items_in_an_order (
    orderid int not null references orders(id),
    itemid int not null references items(id),
    quantity int not null,
    price_paid int not null
);

quantity搬到items_in_an_order. 我total_price更改为更具描述性的price_paid这可能更能描述您想要存储的内容。 这将允许您生成收据。

order_date和order_time被不必要地分成两列。 这使得比较和排序变得尴尬。 将它们放在一个 DATETIME 列中并为其编制索引将允许您按日期/时间进行排序并检查订单是否在日期/时间范围内。

若要获取订单中的所有项目，请执行联接。

SELECT items.itemid
FROM   items
JOIN   items_in_an_order io ON io.itemid = items.id
WHERE  io.orderid = ?

要查找项目所在的所有订单，还要执行联接。

SELECT orders.id
FROM   orders
JOIN   items_in_an_order io ON io.orderid = orders.id
WHERE  io.itemid = ?