Foreign key constraints query in Postgresql

[ondras]

From master.s...@gmail.com on February 05, 2010 15:16:23

Hi,

In postgres constraint names can be named $1, $2, etc.. As a result,
constraint name is not unique. There can be 2 tables with 2 constraints
named $1 for example.

Thats why joining information_schema.table_constraints and
information_schema.constraint_column_usage on constraint_name will bring
not relevant results.

here is my solution for this -

select
--conname,
--t.relname as table,
tf.relname as references_table,
a.attname as column_name,
af.attname as references_field
from
pg_constraint c,
pg_attribute a,
pg_attribute af,
pg_stat_user_tables t,
pg_stat_user_tables tf

where
c.contype='f' and
c.conkey[1]=a.attnum and
c.conrelid=a.attrelid and
c.confkey[1]=af.attnum and
c.confrelid=af.attrelid and
t.relid=c.conrelid and
tf.relid=c.confrelid
and t.relname='".$table."' and a.attname='".$name."'

regards,
Sergey

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=62

[ondras]

From ondrej.zara on February 05, 2010 07:15:53

Hi Sergey,

the PgSQL backend is a contributed code and I am not very familiar with it. Are you
able to provide a patch here?

Thanks,
Ondrej

[ondras]

From master.s...@gmail.com on February 05, 2010 07:38:09

here is the diff output after the changes in backend/php-postgresql/index.php

< /* fk constraints _/
< $qstr = "
< SELECT kku.column_name,
< ccu.table_name AS
references_table,
< ccu.column_name AS
references_field
< FROM information_schema.table_constraints tc
< LEFT JOIN
information_schema.constraint_column_usage ccu
< ON tc.constraint_name =
ccu.constraint_name
< LEFT JOIN information_schema.key_column_usage kku
< ON kku.constraint_name =
ccu.constraint_name
< WHERE constraint_type = 'FOREIGN KEY'
< AND kku.table_name = '".$table."'
< AND kku.column_name = '".$name."'
< ;";

                                          /_ fk constraints */
                          $qstr="

select
--conname,
--t.relname as table,
tf.relname as references_table,
a.attname as column_name,
af.attname as references_field
from
pg_constraint c,
pg_attribute a,
pg_attribute af,
pg_stat_user_tables t,
pg_stat_user_tables tf

where
c.contype='f' and
c.conkey[1]=a.attnum and
c.conrelid=a.attrelid and
c.confkey[1]=af.attnum and
c.confrelid=af.attrelid and
t.relid=c.conrelid and
tf.relid=c.confrelid
and t.relname='".$table."' and a.attname='".$name."'";

[ondras]

From ondrej.zara on February 05, 2010 12:40:17

Status: Accepted
Owner: ondrej.zara

[ondras]

From ondrej.zara on March 15, 2010 13:00:36

Please provide a patch in a standard diff format by attaching file to a comment. Also
please make sure you use the latest trunk index.php, as some changes were recently
done in this file.

Thanks,
Ondrej