CODEWITHSUNDEEP
mysql
Emily Kenpachi
Emily Kenpachi

Reputation: 57

Insert into table but unsure of foreign key

Table Order

oid  payerName address
1       james    1 brown
2       smith     2  smith

Table order_item

oid  type  price
1    AN94  3000
2    AK47  1000

order_item as a foreign from Order.

oid is an auto increment in Order Table but in order_item table it is not(dont know if thats is the right way to do it)

I have an insert statement which inserts into both tables at the same time. I was wonder if it the right to make order_item oid an auto increment as well? because the is not other way I can make it copy the oid from order table.

What is the best approach to this small issue.

Upvotes: 1

Views: 738

Answers (3)

peterm
peterm

Reputation: 92785

You shouldn't make order id field in order_items table to be auto_increment. To obtain a value of auto generated id when you insert a row in orders table use LAST_INSERT_ID() function.

It's OK to have it's own auto_increment id column in order_items. Sometimes it comes very handy (e.g. when you want to update an individual order_item row you can reference it by its own id rather than by a combination of columns).

That being said proposed schema might look like

CREATE TABLE orders
(`order_id` int not null auto_increment primary key, 
 `payerName` varchar(5), 
 `address` varchar(8)
);
CREATE TABLE order_items
(`order_item_id` int not null auto_increment primary key, 
 `order_id` int, 
 `type` varchar(4), 
 `price` decimal(19,2),
 foreign key (order_id) references orders (order_id)
);

Names of id columns has been intentionally renamed in my example for clarity. You don't have to change yours obviously.

Now to insert an order and an order item you do

INSERT INTO orders (`payerName`, `address`)
VALUES ('james', '1 brown');

INSERT INTO order_items (`order_id`, `type`, `price`)
VALUES (LAST_INSERT_ID(), 'AN94', 3000);

Here is SQLFiddle demo

You didn't mention PDO in your question but your code using it might look something like this

try {
    $db = new PDO('mysql:host=localhost;dbname=test', 'user', 'userpwd');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    //$db->beginTransaction();

    $query=$db->prepare("INSERT INTO orders (`payerName`, `address`) VALUES (?, ?)");
    $query->execute(array('james', '1 brown'));

    $order_id = $db->lastInsertId();

    $query=$db->prepare("INSERT INTO order_items (`order_id`, `type`, `price`) VALUES (?, ?, ?)");
    $query->execute(array($order_id, 'AN94', 3000));

    //$db->commit();
} catch (PDOException $e) {
    echo "Exeption: " .$e->getMessage();
}
$query = null;
$db = null;

Upvotes: 1

nyzm
nyzm

Reputation: 2825

Orders

order_id(auto inc), payer_name, address

Order Items

order_item_id(auto inc), order_id, item_id, type, quantity, unit_price

Product

item_id(auto inc), item_name, item_unit_price, item_group

Upvotes: 0

zxc
zxc

Reputation: 1526

Since order_item->oid depends on order->oid, Order should exist first before the order_item

This one is right because the oid(1) exist in the order table

insert into order_item (oid,type,price) values('1','AN94','3000')

This one is wrong because the oid(3) doesn't exist in the order table

insert into order_item (oid,type,price)values('3','sample','3333')

BTW the table order-oid should be a primary key

Upvotes: 0

Related Questions