SQL Code en relaties

In deze case is er sprake van 4 tabellen:

- tbl_order_head
- tbl_order_line
- tbl_product
- tbl_user

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- Zorg er eerst voor dat de schemas base en api bestaan, zie voorgaand hoofdstuk

-- De table tbl_product
CREATE TABLE base.tbl_product (
  id SERIAL,
  title VARCHAR(255),
  price NUMERIC,
  CONSTRAINT tbl_product_pkey PRIMARY KEY(id),
  CONSTRAINT tbl_product_title_key UNIQUE(title)
);

-- De table tbl_user
CREATE TABLE base.tbl_user (
  id SERIAL,
  username VARCHAR(255),
  CONSTRAINT tbl_user_pkey PRIMARY KEY(id)
);

-- De table tbl_order_head
CREATE TABLE base.tbl_order_head (
  id SERIAL,
  user_id INTEGER NOT NULL,
  orderdate DATE DEFAULT now(),
  CONSTRAINT tbl_order_head_pkey PRIMARY KEY(id),
  CONSTRAINT tbl_order_head_fk FOREIGN KEY (user_id)
    REFERENCES base.tbl_user(id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
    NOT DEFERRABLE
);

-- De table tbl_order_line
CREATE TABLE base.tbl_order_line (
  id SERIAL,
  order_id INTEGER NOT NULL,
  product_title VARCHAR(255),
  product_price NUMERIC,
  number_items INTEGER,
  CONSTRAINT tbl_order_line_pkey PRIMARY KEY(id),
  CONSTRAINT tbl_order_line_fk FOREIGN KEY (order_id)
    REFERENCES base.tbl_order_head(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
);

Uit het diagram is de volgende informatie te halen:
- Een gebruiker heeft 0 of meer orders. Je kunt dus aannemen dat er in de tabel tbl_order_head een vreemde sleutel aanwezig is welke verwijst naar id in tbl_user. NULL is niet toegestaan, er MOET dus een GELDIG user_id in staan.
- Een order bestaat uit orderregels. Je kunt dus aannemen dat er in de tabel tbl_order_line een vreemde sleutel aanwezig is welke verwijst naar id in tbl_order_head. NULL is niet toegestaan, er MOET dus een GELDIG order_id in staan. Regels die niet aan een order hangen zijn zinloos :)

Een vreemde sleutel (foreign key) dient hetzelfde datatype te hebben als de primaire sleutel (primary key) waarnaar verwezen wordt. In de declaratie van de referentie kun je opgeven wat het gedrag moet zijn bij bepaalde acties:

Stel dat ik een gebruiker weggooi die nog orders heeft: als er geen relatie zou zijn kan dit "gewoon", je hebt dan echter wel een inconsistente database: In tbl_order_head kan worden verwezen naar een niet-bestaand user_id. Met een relatie kun je dit op verschillende manieren oplossen:

1. Weiger de verwijderactie: ON DELETE RESTRICT, de gebruiker wordt niet verwijderd. Resultaat: de database blijft consistent
2. Verwijder de gekoppelde orders: ON DELETE CASCADE, de gebruiker wordt verwijderd en de orders welke gekoppeld zijn aan de betreffende gebruiker. Resultaat: de database blijft consistent.
3. Pas het user_id in tbl_order_head aan naar NULL: ON DELETE SET NULL, de gebruiker wordt verwijderd, de orders blijven bestaan maar zijn niet meer gekoppeld aan een gebruiker. Resultaat: de database blijft consistent.

Deze opties bestaan voor zowel UPDATE als DELETE-acties.
Het uitgangspunt van deze relaties is dus de data consistent te laten blijven.

In deze case heb ik voor de relatie tussen gebruikers en orders voor optie 1 gekozen, het is dus niet mogelijk een gebruiker te verwijderen indien deze orders heeft gemaakt. Bij de relatie tussen orders en orderregels heb ik voor optie 2 gekozen, bij het verwijderen van een order worden de bijbehorende orderregels dus ook verwijderd.

Uit de SQL-dump zal ik de DDL (Data Definition Language) van tbl_order_line uitleggen:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

-- De table tbl_order_line
CREATE TABLE base.tbl_order_line (
  id SERIAL,
  order_id INTEGER NOT NULL,
  product_title VARCHAR(255),
  product_price NUMERIC,
  number_items INTEGER,
  CONSTRAINT tbl_order_line_pkey PRIMARY KEY(id),
  CONSTRAINT tbl_order_line_fk FOREIGN KEY (order_id)
    REFERENCES base.tbl_order_head(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
);

Er liggen in deze tabel 2 vormen van beperkingen (CONSTRAINT) op de kolommen: id wordt gemarkeerd als een primaire sleutel, order_id als een vreemde sleutel. Bij de declaratie van de vreemde sleutel geef je op naar welke tabel en kolom je verwijst, in dit geval verwijst order_id naar het veld id in tbl_order_head. De ON DELETE CASCADE en ON UPDATE CASCADE zorgt ervoor dat records uit tbl_order_line worden verwijderd als een order uit tbl_order_head wordt verwijderd, of dat de order_id’s in tbl_order_line worden bijgewerkt als het id in tbl_order_head wordt bijgewerkt, dit laatste is onwaarschijnlijk, maar geeft nog steeds garantie op consistente data.

De tekst “DEFERRABLE” kun je vertalen naar uitstelling of opschorting. Via dit statement geef je aan of een overtreding van een foreign key relatie mag worden uitgesteld (bijvoorbeeld tijdens een transactie), de standaard waarde is “NOT DEFERRABLE” en geeft dus aan dat een overtreding meteen ingaat wanneer een ongeldige executie plaatsvindt.

Meer informatie over deze zgn. "foreign key relaties" vind je op: http://www.postgresql.org/docs/8.2/static/ddl-constraints.html#DDL-CONSTRAINTS-FK

Meer informatie over de create table statements vind je op:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

« vorige pagina | volgende pagina »

« Lees de omschrijving en reacties

Forum berichten

Reacties

PHP scripts

PHP tutorials

Actief op PHPhulp

SQL Code en relaties

Inhoudsopgave

PHP tutorial opties

Over PHPhulp

Support

Gesponsord

Extra's

Privacy