Create the order_items Table

The order_items table

Let’s start this lesson by creating a new table.

The order_items table will hold the details of an order.

We need to create two foreign keys for our new table to enforce the referential integrity between the tables. One foreign key will relate the order_items table to the orders table. The second foreign key will relate order_items table to the products table.

Later on, we’ will add the foreign key constraints with alter table commands. But first, let’s create the table:

create table order_items (id int(11) not null auto_increment, order_id int(11) not null, product_id int(11) not null, price numeric(15, 2) not null, quantity int(11), primary key(id));

• It has an id, which is the primary key of the table. It’s mandatory (not null) and is automatically assigned incrementally (auto_increment).
• The columns that will be used for referential integrity between the order_items table and the orders and products tables are order_id and product_id, respectively. The type for both of these columns is int(11) and matches the type of id

...