I have only done databases without relations, but now I need to do something more serious and correct.
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)
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.