How can I insert into tables with relations?

Answer 1

I think you already have the hang of what needs to happen. But what I think you are getting at is how to ensure data integrity.

This is where Transactions become important.

http://www.sqlteam.com/article/introduction-to-transactions

Answer 2

You should first create an order and then insert products in the table Product_Orders. This is necessary because you need an actual order with an id to associate it with the table Product_Orders.

You always should create a record in the foreign-key table before being able to create one in your current table. That way you should create a "Post", customer, type, product, order and product_order.

Answer 3

Is it the SalesPrice (I'm guessing that's what SamletPris means) that's causing the issue? I can see that being a problem here. One common design solution is to have 2 tables: Order and OrderLine. The Order is a header table - it will have the foreign key relationship to the Customer table, and any other 'top level' data. The OrderLine table has FK relationships to the Order table and to the Product table, along with quantity, unit price, etc. that are unique to an order's line item. Now, to get the sales price for an order, you sum the (unit price * quantity) of the OrderLine table for that order. Storing the SalesPrice for a whole order is likely to cause big issues down the line.

Answer 4

Try this ...

first you have to insert a customer

insert into kunde values(1, 'navn', 1, 'adresse', 1)

then you insert a type

insert into VareGruppe values(1, 'Type1')

then you insert a product

insert into vare values(1, 'product1', '10.0', 1, 1)

then you add an order

insert into ordre values(1, 1, '20090101', '10.0')

then you insert a register to the product_orders table

insert into VareOrdre values (1, 1, 1, 1)

I think this is it. :-)

As the primary keys are autoincrement, don't add them to the insert and specify the columns like this

insert into vare(Nav, Pris, Beholdning, VGnr) values('product1', '10.0', 1, 1)

Use Select @@identity to see the onr value

Answer 5

A note just in case this is MySQL: If you're using MyISAM, the MySQL server ignores the foreign keys completely. You have to set the engine to InnoDB if you want any kind of integrity actually enforced on the database end instead of just in your logic. This isn't your question but it is something to be aware of.

fbinder got the question right :)