CODEWITHSUNDEEP
sqlforeign-keysprimary-keyentity-relationship
CasperT
CasperT

Reputation: 3475

How can I insert into tables with relations?

I have only done databases without relations, but now I need to do something more serious and correct.

Here is my database design: alt text

  1. Kunde = Customer
  2. Vare = Product
  3. Ordre = Order (Read: I want to make an order)
  4. VareGruppe = ehm..type? (Read: Car, chair, closet etc.)
  5. VareOrdre = Product_Orders

Here is my SQL (SQLite) schema:

CREATE TABLE Post (
    Postnr INTEGER NOT NULL PRIMARY KEY,
    Bynavn VARCHAR(50) NOT NULL
);

CREATE TABLE Kunde (
    CPR INTEGER NOT NULL PRIMARY KEY,
    Navn VARCHAR(50) NOT NULL,
    Tlf INTEGER NOT NULL,
    Adresse VARCHAR(50) NOT NULL,
    Postnr INTEGER NOT NULL 
    CONSTRAINT fk_postnr_post REFERENCES Post(Postnr)
);

CREATE TABLE Varegruppe (
    VGnr INTEGER PRIMARY KEY,
    Typenavn VARCHAR(50) NOT NULL
);


CREATE TABLE Vare (
    Vnr INTEGER PRIMARY KEY,
    Navn VARCHAR(50) NOT NULL,
    Pris DEC NOT NULL,
    Beholdning INTEGER NOT NULL,
    VGnr INTEGER NOT NULL
        CONSTRAINT fk_varegruppevgnr_vgnr REFERENCES Varegruppe(VGnr)
);

CREATE TABLE Ordre (
    Onr INTEGER PRIMARY KEY,
    CPR INTEGER NOT NULL
        CONSTRAINT fk_kundecpr_cpr REFERENCES Kunde(CPR),
    Dato DATETIME NOT NULL,
    SamletPris DEC NOT NULL
);

CREATE TABLE VareOrdre (
    VareOrdreID INTEGER PRIMARY KEY,
    Onr INTEGER NOT NULL
        CONSTRAINT fk_ordrenr_onr REFERENCES Ordre(Onr),
    Vnr INTEGER NOT NULL 
        CONSTRAINT fk_varevnr_vnr REFERENCES Vare(Vnr),
    Antal INTEGER NOT NULL
);

It should work correctly.

But I am confused about Product_Orders.

How do I create an order? For example, 2 products using SQL INSERT INTO? I can get nothing to work.

So far:

Only when I manually insert products and data into Product_Orders and then add that data to Orders = which makes it complete. Or the other way around (create an order in with 1 SQL, then manually inserting products into Product_orders - 1 SQL for each entry)

Upvotes: 2

Views: 27565

Answers (5)

João Guilherme
João Guilherme

Reputation: 1379

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

Upvotes: 4

Fabio Vinicius Binder
Fabio Vinicius Binder

Reputation: 13214

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.

Upvotes: 6

Matt Brunmeier
Matt Brunmeier

Reputation: 1310

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

Upvotes: 0

Harper Shelby
Harper Shelby

Reputation: 16581

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.

Upvotes: 0

Matthew Vines
Matthew Vines

Reputation: 27581

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

Upvotes: 1

Related Questions