[PGSQL] Foreign keys
Ik ben nog steeds met mn shoppingcart bezig maar ik snap nog steeds niet hoe ik nu foreign keys aanmaak. Ik snap wel hoe het moet maar niet het idee erachter ik heb bijvoorbeeld:
products:
product_id(serial)
product_name(varchar)
product_cat(int4)
categories:
cat_id(serial)
cat_name(varchar)
cat_parent(int4)
Deze hebben uiteraard een verband met elkaar alleen snap ik niet hoe ik nu precies deze keys aan maak. Ik wil dat als een categorie wordt verwijderd. dat ook alle producten hier worden verwijderd(cascade). Maar ik wil ook dat een categorie pas verwijderd kan worden als deze geen onderliggende meer heeft (dus een lege categorie). Hoe maak ik dus nu de foreign keys aan, ik heb de tuts van pgFrank gelezen (meerdere malen). Maar snap nog steeds niet op welke kolommen ik keys moet aanmaken.
Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas
Edit: Je moet dus weten wat de relaties tussen diverse soorten data zijn en hoeveel relaties er zijn. 1 product kan bij 1 categorie horen, maar het zouden er ook meerdere kunnen zijn. Dat zijn keuzes die jij moet maken. 1 categorie kan 1 parent hebben, maar dat zouden er ook meerdere kunnen zijn, nog meer keuzes die jij mag maken! Op basis van deze beslissingen ga je een datamodel opstellen en krijg je de foreign keys cadeau.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Code (php)
1
2
3
4
5
2
3
4
5
ALTER TABLE products
ADD FOREIGN KEY (product_cat)
REFERENCES categories (cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
ADD FOREIGN KEY (product_cat)
REFERENCES categories (cat_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
Nu worden alle producten gewijzigd als een categorie gewijzigd wordt maar is het niet mogelijk een categorie te verwijderen zolang er nog producten aan gekoppeld zijn.
pgFrank schreef op 13.03.2008 12:41:
Ga normaliseren, dan blijkt vanzelf hoe en waarom e.e.a. aan elkaar moet worden geknoopt. Jouw probleem heeft verder ook helemaal niets met PostgreSQL te maken, het draait om de relaties. En die kun je in iedere relationele database maken.
Ja dat snap ik wel waarom het allemaal gekoppeld moet worden maar niet hoe. Moet ik product_cat(products) aan cat_id(categories) koppelen of juist net andersom.
users:
id
naam
topics:
id
id_user
content
Ieder topic heeft nu een eigenaar, id_user, daarmee kun je de naam van de user uiteindelijk opvragen. id_user zal dus moeten verwijzen naar het id in de tabel 'users'. De foreign key komt in de tabel 'topics' te staan en verwijst naar de tabel 'users', de kolom 'id'. Dat is namelijk de parent.
Een parent weet niet welke childs er allemaal zijn, dat is nergens voor nodig. Er wordt tenslotte geen kinderbijslag verstrekt... ;)
Normaliseren, zie de derde normaalvorm.
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
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
CREATE TABLE shoppingcart.products
(
product_id serial NOT NULL,
product_name varchar NOT NULL,
cat_id int4 NOT NULL,
CONSTRAINT pk_products PRIMARY KEY (product_id),
CONSTRAINT fk_products_cats FOREIGN KEY (cat_id)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT un_product_name UNIQUE (product_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.products OWNER TO jurgen;
CREATE TABLE shoppingcart.categories
(
cat_id serial NOT NULL,
cat_name varchar NOT NULL,
cat_parent int4,
CONSTRAINT pk_categories PRIMARY KEY (cat_id),
CONSTRAINT fk_categories FOREIGN KEY (cat_parent)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ub_categories UNIQUE (cat_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.categories OWNER TO jurgen;
(
product_id serial NOT NULL,
product_name varchar NOT NULL,
cat_id int4 NOT NULL,
CONSTRAINT pk_products PRIMARY KEY (product_id),
CONSTRAINT fk_products_cats FOREIGN KEY (cat_id)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT un_product_name UNIQUE (product_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.products OWNER TO jurgen;
CREATE TABLE shoppingcart.categories
(
cat_id serial NOT NULL,
cat_name varchar NOT NULL,
cat_parent int4,
CONSTRAINT pk_categories PRIMARY KEY (cat_id),
CONSTRAINT fk_categories FOREIGN KEY (cat_parent)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ub_categories UNIQUE (cat_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.categories OWNER TO jurgen;
Zit ik een beetje in de goede richting?
1 product heeft nu 1 categorie.
1 categorie kan 1 parent hebben.
Ga er eens mee testen, vooral het updaten en verwijderen van data is interessant, maar ook de selects mag je niet vergeten. Krijg je nu echt die resultaten die je verwacht? That's the question!
Code (php)
1
2
3
2
3
ALTER TABLE shoppingcart.categories ADD CONSTRAINT fk_categories_products FOREIGN KEY (cat_id) REFERENCES shoppingcart.products (cat_id)
ON UPDATE CASCADE ON DELETE CASCADE;
CREATE INDEX fki_categories_products ON shoppingcart.categories(cat_id);
ON UPDATE CASCADE ON DELETE CASCADE;
CREATE INDEX fki_categories_products ON shoppingcart.categories(cat_id);
en de foutmelding
Code (php)
1
2
3
2
3
Een fout is opgetreden:
ERROR: there is no unique constraint matching given keys for referenced table "products"
ERROR: there is no unique constraint matching given keys for referenced table "products"
Hoe kan ik dit verhelpen, mijn huidige opstelling ziet er zo uit:
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE shoppingcart.products
(
product_id serial NOT NULL,
product_name varchar NOT NULL,
cat_id int4 NOT NULL,
CONSTRAINT pk_products PRIMARY KEY (product_id),
CONSTRAINT un_product_name UNIQUE (product_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.products OWNER TO jurgen;
CREATE TABLE shoppingcart.categories
(
cat_id serial NOT NULL,
cat_name varchar NOT NULL,
cat_parent int4,
CONSTRAINT pk_categories PRIMARY KEY (cat_id),
CONSTRAINT fk_categories FOREIGN KEY (cat_parent)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ub_categories UNIQUE (cat_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.categories OWNER TO jurgen;
(
product_id serial NOT NULL,
product_name varchar NOT NULL,
cat_id int4 NOT NULL,
CONSTRAINT pk_products PRIMARY KEY (product_id),
CONSTRAINT un_product_name UNIQUE (product_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.products OWNER TO jurgen;
CREATE TABLE shoppingcart.categories
(
cat_id serial NOT NULL,
cat_name varchar NOT NULL,
cat_parent int4,
CONSTRAINT pk_categories PRIMARY KEY (cat_id),
CONSTRAINT fk_categories FOREIGN KEY (cat_parent)
REFERENCES shoppingcart.categories (cat_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT ub_categories UNIQUE (cat_name)
)
WITHOUT OIDS;
ALTER TABLE shoppingcart.categories OWNER TO jurgen;
Gewijzigd op 01/01/1970 01:00:00 door Jurgen assaasas
Jurgen schreef op 13.03.2008 13:36:
Wat zeg ik net? Een parent heeft geen flauw idee of deze childs heeft, dat is ook nergens voor nodig. Dat kun je dus ook niet met een FK vaststellen, een FK geeft aan welke child bij welke parent hoort. De parent weet echt helemaal nergens vanaf!waar al een FK op zit die controleert of hij geen childs heeft
Verander je echter iets aan de parent, dan zal de child dit signaleren dankzij de FK. Afhankelijk van de instellingen van de FK zullen er dan bepaalde acties op de child worden uitgevoerd, bv. ook verwijderen, of wordt de wijziging van de parent onmogelijk gemaakt: RESTRICT.
Jurgen schreef op 13.03.2008 14:03:
Dat klopt, maar het zijn de childs die hier voor zorgen! Die houden het verwijderen van de parent tegen, de FK van de child op de parent houdt dit tegen.Ik snap het even niet Frank, zoals ik het nu heb, kan er geen categorie verwijderd worden als deze nog childs hebben dit is toch goed? Dan krijg je dus nooit ongekoppelde data.
De parent weet nergens van, die ontkent alles, heeft volgens hem geen childs. Waarom niet? Omdat dit nergens is vastgelegd. De childs houden bij wie hun parent is. Wil je weten welke childs bij een parent horen, zul je dus alle childs moeten opvragen en dan controleren of zij toevallig parent X hebben.