Foreign Key informatie
In MySQL heb je wel bijvoorbeeld SHOW KEYS FROM tabel maar die geeft niet de informatie die ik wil hebben namelijk: de tabel waarnaar verwezen wordt en de kolom in die tabel waar naar verwezen wordt.
Weet iemand toevallig hoe ik dit voor elkaar kan krijgen?
In eerste instantie zoek ik dit voor MySQL maar als iemand het voor pgSQL weet is dat ook welkom.
Gewijzigd op 01/01/1970 01:00:00 door Bo az
Code (php)
1
SHOW CREATE TABLE jouw_tabelnaam;
Het resultaat mag je dan verder gaan uitpluizen, maar de informatie over de FK staat er tussen.
PostgreSQL:
Code (php)
1
SELECT * FROM pg_constraint WHERE contype = 'f';
Dit levert alle foreign key contstraints in je database op, inclusief de betrokken tabel- en kolomnamen. Met functies als pg_class en pg_attribute kun dan nog meer details op gaan vragen. Door in de WHERE ook een beperking te leggen op conrelid, kun je zoeken op een specifieke tabel.
Edit:
Deze query is fraaier, uitgewerkt:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
c.*,
p.*
FROM
pg_constraint AS c
JOIN pg_class AS p ON c.conrelid = p.relfilenode
WHERE
c.contype = 'f'
AND
p.relname IN('jouw_tabelnaam');
c.*,
p.*
FROM
pg_constraint AS c
JOIN pg_class AS p ON c.conrelid = p.relfilenode
WHERE
c.contype = 'f'
AND
p.relname IN('jouw_tabelnaam');
Met de resultaten kun je dan weer verder gaan spitten.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
MySQL:
SHOW CREATE TABLE kende ik ook, maar dat is nog al een gepluis, is er geen andere (lees: makkelijkere) manier?
pg:
Ik ben nog een newbee als het gaat om pg, helemaal met arrays ed.
En als ik me niet vergis heb je die nodig om te weten te komen welke kolom(en) er bij de fk horen. Ik dacht zelf gezien jouw (pgFrank) voorbeeld en een comment bij de link aan de volgende join:
Code (php)
1
JOIN pg_attribute a ON a.attnum = ANY(c.confkey)
Maar die lijkt niet helemaal lekker te werken (, waar vast een logische verklaring voor is).
Edit:
Niet lekker te werken is misschien te vaag, ik krijg gewoon veel te veel dubbele resultaten.
Gewijzigd op 01/01/1970 01:00:00 door Bo az
confrelid is het OID van de tabel die de FK bevat
conrelid is het OID van de tabel waar de FK naar verwijst.
pg_class:
conkey is het kolomnummer (of numers) die in een array staat/staan van de tabel die de FK bevat
confkey is het kolomnummer s het kolomnummer (of numers) die in een array staat/staan van de tabel waar de FK naar verwijst.
Haal eerst eens de FK's op en de OID's van de betrokken tabellen. Tevens de array's, dan kun je gaat kijken hoe dit nu inelkaar steekt.
pg_attribute:
attrelid is het OID van de tabel en attnum is het volgorde-nummer van de kolom in de tabel. Deze kun je dus uit de array plukken die in conkey of confkey staat.
Je kunt de functie array_to_string() gebruiken om de array om te zetten naar een string (duhhh) en gescheiden door een komma in een IN()-vergelijking te zetten. Wel zo handig en begrijpelijk.
Het kan wellicht fraaier, maar met dit soort constructies (die overigens erg leuk zijn!) heb ik nog maar weinig gewerkt.
Succes!
Ps. Laat je ook even zien wat het is geworden? Kan ik vast wat van leren.
Die vele resultaten kwamen dus omdat ik de OID's niet mee nam in de join...
Ik heb nu het volgende:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
p.relname AS tabel_naam,
array_to_string(c.conkey, ', ') AS kolomen,
a.attname AS kolom_naam,
p2.relname AS reference_tabel_naam,
array_to_string(c.confkey, ', ') AS reference_kolomen
FROM
pg_constraint AS c
JOIN pg_attribute AS a ON (a.attnum = ANY(c.conkey) AND a.attrelid=c.conrelid)
JOIN pg_class AS p ON (c.conrelid = p.relfilenode)
JOIN pg_class AS p2 ON (c.confrelid = p2.relfilenode)
WHERE
c.contype = 'f'
AND
p.relname = 'test_reference';
p.relname AS tabel_naam,
array_to_string(c.conkey, ', ') AS kolomen,
a.attname AS kolom_naam,
p2.relname AS reference_tabel_naam,
array_to_string(c.confkey, ', ') AS reference_kolomen
FROM
pg_constraint AS c
JOIN pg_attribute AS a ON (a.attnum = ANY(c.conkey) AND a.attrelid=c.conrelid)
JOIN pg_class AS p ON (c.conrelid = p.relfilenode)
JOIN pg_class AS p2 ON (c.confrelid = p2.relfilenode)
WHERE
c.contype = 'f'
AND
p.relname = 'test_reference';
Dit werkt prima, ook voor samengestelde fk's.
Maar zoals je misschien al ziet mis ik nog 1 ding, namelijk de kolom in de tabel waarnaar verwezen wordt.
Daarvoor zou ik nog een JOIN op pg_attribute moeten toevoegen (toch?).
Deze:
Code (php)
1
JOIN pg_attribute AS a2 ON (a2.attnum = ANY(c.confkey) AND a2.attrelid=c.confrelid)
Maar de query die daar uit volgt geeft in het geval van samengestelde fk's dubbele resultaten. Wat ook logisch is omdat er nu op 2 resultaten 2x gejoind wordt (door de array).
Enig idee hoe ik dit kan oplossen?
De volledige query (met dubbele resultaten:)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
p.relname AS tabel_naam,
array_to_string(c.conkey, ', ') AS kolomen,
a.attname AS kolom_naam,
p2.relname AS reference_tabel_naam,
array_to_string(c.confkey, ', ') AS reference_kolomen,
a2.attname AS reference_kolom_naam
FROM
pg_constraint AS c
JOIN pg_attribute AS a ON (a.attnum = ANY(c.conkey) AND a.attrelid=c.conrelid)
JOIN pg_attribute AS a2 ON (a2.attnum = ANY(c.confkey) AND a2.attrelid=c.confrelid)
JOIN pg_class AS p ON (c.conrelid = p.relfilenode)
JOIN pg_class AS p2 ON (c.confrelid = p2.relfilenode)
WHERE
c.contype = 'f'
AND
p.relname = 'test_reference';
p.relname AS tabel_naam,
array_to_string(c.conkey, ', ') AS kolomen,
a.attname AS kolom_naam,
p2.relname AS reference_tabel_naam,
array_to_string(c.confkey, ', ') AS reference_kolomen,
a2.attname AS reference_kolom_naam
FROM
pg_constraint AS c
JOIN pg_attribute AS a ON (a.attnum = ANY(c.conkey) AND a.attrelid=c.conrelid)
JOIN pg_attribute AS a2 ON (a2.attnum = ANY(c.confkey) AND a2.attrelid=c.confrelid)
JOIN pg_class AS p ON (c.conrelid = p.relfilenode)
JOIN pg_class AS p2 ON (c.confrelid = p2.relfilenode)
WHERE
c.contype = 'f'
AND
p.relname = 'test_reference';
ps. Zijn die arrays niet een enorme overtreding op de 1e normaalvorm?
Boaz schreef op 07.03.2008 15:13:
Wie zegt dat er hier wordt genormaliseerd? Je hebt het over een weergave van een SELECT-query en dat kan prima met array's. Dat ik zelf niet zo'n liefhebber ben van array's in de database, dat is weer een ander verhaal. Ze zijn hier goed bruikbaar, zeker omdat je met dit soort functies toch vooral binnen de database aan het rommelen bent en niet daarbuiten.ps. Zijn die arrays niet een enorme overtreding op de 1e normaalvorm?
Wat jij voor een applicatie aan het maken bent, is mij dan ook een raadsel. pgAdmin3 is een uitstekende tool en PhpPgAdmin volstaat voor het lokale werk op de server.
Ps. De query lijkt goed te werken, ik zit hem even te testen op een datamodel dat ik hier heb liggen en krijg de juiste resultaten. Geef de tabel op die fk's bevat en ik krijg keurig de parent-tabellen en de kolommen waar de FK op ligt.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE test (
pk_1 CHAR(10),
pk_2 CHAR(10),
nogiets VARCHAR(100),
PRIMARY KEY (pk_1, pk_2)
);
CREATE TABLE test_reference (
pk SERIAL PRIMARY KEY,
fk_1 CHAR(10) NOT NULL,
fk_2 CHAR(10) NOT NULL,
FOREIGN KEY (fk_1, fk_2) REFERENCES test (pk_1, pk_2)
);
pk_1 CHAR(10),
pk_2 CHAR(10),
nogiets VARCHAR(100),
PRIMARY KEY (pk_1, pk_2)
);
CREATE TABLE test_reference (
pk SERIAL PRIMARY KEY,
fk_1 CHAR(10) NOT NULL,
fk_2 CHAR(10) NOT NULL,
FOREIGN KEY (fk_1, fk_2) REFERENCES test (pk_1, pk_2)
);
Quote:
Wat jij voor een applicatie aan het maken bent, is mij dan ook een raadsel.
Ik wil automatisch formulieren genereren, dat wil dus zeggen dat ik bijvoorbeeld bij een DATE veld een kalender wil weergeven etc.
Omdat een foreign key een beperking op de mogelijkheden legt wil ik daar dus een select element hebben.
Onder water zal ik dus de kolom(men) van de parent tabel moeten ophalen om te weten welke opties er in die select moeten komen.
Code (php)
1
2
3
4
5
2
3
4
5
SELECT *
FROM information_schema.KEY_COLUMN_USAGE WHERE
REFERENCED_TABLE_NAME IS NOT NULL
AND
TABLE_NAME='test_reference'
FROM information_schema.KEY_COLUMN_USAGE WHERE
REFERENCED_TABLE_NAME IS NOT NULL
AND
TABLE_NAME='test_reference'
Edit:
Oeps, dit is eigenlijk een bump :$
Gewijzigd op 01/01/1970 01:00:00 door Bo az
Gewijzigd op 01/01/1970 01:00:00 door Bo az
;)