mysql - two columns primary key, auto-increment and foreign key -

the following query successful when enter null values order id , different values(those not exist in column) item id, order id still getting incremented. how retain same order id? there problem way foreign key has been stated?

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

the problem stems trying one-to-many (order items) relationship in single table. end badly. need two. 1 order, 1 items in order. or bad, how relational databases lists.

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 moves items_in_an_order. changed total_price more descriptive price_paid more descriptive of want storing. let produce receipts.

order_date , order_time unnecessarily split 2 columns. makes comparison , sorting awkward. putting them 1 datetime column , indexing let sorting date/time , check if order inside date/time range.

to items in order, do join.

select items.itemid   items join   items_in_an_order io on io.itemid = items.id  io.orderid = ? 

to find orders item in, join.

select orders.id   orders join   items_in_an_order io on io.orderid = orders.id  io.itemid = ?