From: | Svenne Krap <svenne(dot)lists(at)krap(dot)dk> |
---|---|
To: | sql pgsql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Foreign key columns |
Date: | 2009-11-05 21:08:09 |
Message-ID: | 4AF33EB9.4060901@krap.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi.
Is there a simple way to get foreign key data... for example I found a
view, that does what I want ...
It delivers
fk_table | fk_column | pk_table | pk_column | constraint_name
--------------+--------------------+-------------------+-----------+--------------------------------------
organisation | customer_rep | person | id | organisation_customer_rep_fkey
organisation | ekstra_skema | ekstra_skema | id | org_schema_fkey
organisation | in_group | organisation | id | organisation_in_group_fkey
organisation | org_paying_company | organisation | id | organisation_org_paying_company_fkey
organisation | primary_contact | person | id | primary_contact_fkey
organisation | type | organisation_type | id | organisation_type_fkey
The query in question is
SELECT FK.TABLE_NAME as FK_Table, CU.COLUMN_NAME as FK_Column,
PK.TABLE_NAME as PK_Table, PT.COLUMN_NAME as PK_Column,
C.CONSTRAINT_NAME as Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME
= FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME =
CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY') PT ON PT.TABLE_NAME =
PK.TABLE_NAME WHERE FK.TABLE_NAME='organisation' ORDER BY 1,2,3,4;
The only problem is that this query is sloooooow, runs in tens of seconds...
Is there a good native (i.e. fast) pgsql-query to find that type of
information?
Svenne
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-11-05 22:21:50 | Re: Foreign key columns |
Previous Message | Pavel Stehule | 2009-11-05 20:55:53 | Re: Converting T-SQL to PostgreSQL |