如何插入具有关系的表？

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 http://i41.tinypic.com/27xqzxl.jpg

这是我的数据库设计:alt text http://i41.tinypic.com/27xqzxl.jpg

Kunde = Customer

Kunde =客户

Vare = Product

Vare =产品

Ordre = Order (Read: I want to make an order)

Ordre =订单(阅读:我想订购)

VareGruppe = ehm..type? (Read: Car, chair, closet etc.)

VareGruppe = ehm..type? (阅读:汽车,椅子,壁橱等)

VareOrdre = Product_Orders

VareOrdre = Product_Orders

Here is my SQL (SQLite) schema:

这是我的SQL(SQLite)架构:

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.

但我对Product_Orders感到困惑。

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

如何创建订单?例如,2个产品使用SQL INSERT INTO?我无能为力。

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)

只有当我手动将产品和数据插入到Product_Orders中,然后将该数据添加到Orders =才能完成。或者反过来(使用1个SQL创建订单,然后手动将产品插入Product_orders - 每个条目1个SQL)

5 个解决方案

#1

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.

您应首先创建订单,然后在表Product_Orders中插入产品。这是必要的,因为您需要一个带有id的实际订单才能将其与表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.

您总是应该在外键表中创建一个记录,然后才能在当前表中创建一个记录。这样你就可以创建一个“发布”,客户,类型,产品,订单和product_order。

#2

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

然后您将一个寄存器插入product_orders表

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

使用Select @@ identity查看onr值

#3

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

#4

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.

这是导致问题的SalesPrice(我猜这是SamletPris的意思)吗?我可以看到这是一个问题。一种常见的设计解决方案是拥有2个表:Order和OrderLine。 Order是一个标题表 - 它将具有与Customer表的外键关系,以及任何其他“*”数据。 OrderLine表与Order表和Product表具有FK关系,以及订单行项目唯一的数量,单价等。现在,要获取订单的销售价格,请将该订单的OrderLine表的(单价*数量)相加。为整个订单存储SalesPrice可能会导致重大问题。

#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.

请注意以下是MySQL:如果您使用的是MyISAM,MySQL服务器会完全忽略外键。如果您希望在数据库端实际执行任何类型的完整性而不是仅仅在逻辑中,则必须将引擎设置为InnoDB。这不是你的问题,但需要注意的是。

fbinder got the question right :)

fbinder得到了正确的问题:)

#1