foreign key's in system tables

Поиск
Список
Период
Сортировка
От Tjibbe Rijpma
Тема foreign key's in system tables
Дата
Msg-id BAY115-DAV882CE214C96F40B3A2C53DD2F0@phx.gbl
обсуждение исходный текст
Ответ на Problem UPDATE Statement  (<operationsengineer1@yahoo.com>)
Ответы Re: foreign key's in system tables
Список pgsql-novice
Дерево обсуждения
Hi,
 
From a table I want to know all the foreign key en their references. This goes well, but if there is more than one column in a FOREIGN KEY is goes wrong.
I have trouble with the arrays: conkey and confkey. 
 
Can anyone help me?
 
Greetings Tjibbe
 
 
 
CREATE TABLE mens (
 voornaam TEXT,
 achternaam TEXT,
 UNIQUE (voornaam, achternaam)
);
 
CREATE TABLE inwoner (
 inwoner_voornaam TEXT,
 inwoner_achternaam TEXT,
 FOREIGN KEY (inwoner_voornaam, inwoner_achternaam) REFERENCES mens (voornaam, achternaam)
);
 

SELECT a_fk.attname as fk_column, ref.relname as ref_table, a_ref.attname as ref_column
FROM pg_constraint c, pg_class fk, pg_attribute a_fk, pg_class ref,  pg_attribute a_ref
WHERE c.conrelid = fk.oid
AND a_fk.attrelid = fk.oid
AND c.confrelid = ref.oid
AND a_ref.attrelid = ref.oid
AND c.contype = 'f'
AND fk.relname = 'inwoner'
AND a_fk.attnum = ANY (c.conkey)
AND a_ref.attnum = ANY (c.confkey)
 
 
Result:
 
fk_column          | ref_table | ref_column
--------------------------------------------
inwoner_voornaam   | mens      | voornaam
inwoner_voornaam   | mens      | achternaam
inwoner_achternaam | mens      | voornaam
inwoner_achternaam | mens      | achternaam

 

В списке pgsql-novice по дате отправления: