Foreign key constraints query in Postgresql

[GoogleCodeExporter]

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 reported on code.google.com by master.s...@gmail.com on 5 Feb 2010 at 2:16

[GoogleCodeExporter]

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

Original comment by ondrej.zara on 5 Feb 2010 at 3:15

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

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

Original comment by master.s...@gmail.com on 5 Feb 2010 at 3:38

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Original comment by ondrej.zara on 5 Feb 2010 at 8:40

• Changed state: Accepted
• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

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

Original comment by ondrej.zara on 15 Mar 2010 at 8:00

• Added labels: ****
• Removed labels: ****