Foreign Key informatie

MySQL:

SHOW CREATE TABLE jouw_tabelnaam;

Het resultaat mag je dan verder gaan uitpluizen, maar de informatie over de FK staat er tussen.

PostgreSQL:

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:

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');

Met de resultaten kun je dan weer verder gaan spitten.

Ik ben weer bezig ;)

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:

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.[/edit]

pg_constraint:
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.

Ik ben er nu bijna!

Die vele resultaten kwamen dus omdat ik de OID's niet mee nam in de join...

Ik heb nu het volgende:

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';

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:

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:)

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';

ps. Zijn die arrays niet een enorme overtreding op de 1e normaalvorm?

Boaz schreef op 07.03.2008 15:13

ps. Zijn die arrays niet een enorme overtreding op de 1e normaalvorm?

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.

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.

Werkt prima, totdat je samengestelde pk/fk's hebt probeer 'm maar eens hier op uit:

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)
);

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.

Ik heb inmiddels wel het juiste resultaat te pakken met MySQL:

SELECT * 
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 :$

Een late reactie, maar het is ook bij postgre gelukt en wel via 'information_schema' dat is ansi sql en dus redelijk standaard. Helaas zitten er toch nog wel de nodige verschillen in met mysql.

Uiteraard zijn er verschillen, je zult vooral veel meer informatie krijgen. Al is dat een klein gokje, ik ben niet zó bekend met de details van MySQL... Ik gebruik hem vooral om afscheid van te nemen. Surprise surprise, dat hadden jullie vast niet verwacht!

;)