PostgreSQL foreign key

Question

how to detect if a column of a table is set by a foreign key, and get the name of the referenced table, in Postgres?

I need this Information for a java GUI. So SQL solution is the best way it could be solved, i really can't manage it.

regards stefan

example:

create table SUREALTABLE(
    VNR varchar(5),
    tnumberone integer,
    tnumbertwo integer,
    foreign key (tnumberone ,tnumbertwo) references TESTTABLE(numberone,numbertwo),
    primary key (VNR)
);

create table TESTTABLE(
    numberone integer,
    numbertwo integer,
    primary key (numberone, numbertwo)
);

yieldsfalsehood · Accepted Answer · 2014-01-09 20:27:57Z

2

You can determine that with pg_catalog.pg_constraint and pg_catalog.pg_attribute (more info here).

select a.confrelid::regclass,
       b.attname
from pg_constraint a
       join pg_attribute b
         on a.conrelid = b.attrelid
         and b.attnum = any (a.conkey)
where a.conrelid = '<tablename>'::regclass
  and a.contype = 'f'
;

You can filter that down using b.attname.

More concrete example:

select a.confrelid::regclass
from pg_constraint a
       join pg_attribute b
         on a.conrelid = b.attrelid
         and b.attnum = any (a.conkey)
where a.conrelid = 'SUREALTABLE'::regclass
  and a.contype = 'f'
  and b.attname = 'tnumberone'
;

This returns "testtable", indicating that the column "tnumberone" of the table "surealtable" has a foreign key reference to the table "testtable".

edited Jan 9, 2014 at 20:27

answered Jan 9, 2014 at 18:49

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stefan Sprenger Over a year ago

could you please give example how that will look with some variables.. so as an example we have a table named T1 and a table T2. T1 foreign t2.ID references from(t2.id)

yieldsfalsehood Over a year ago

Sure. Can you edit your question to add your schema? Your create table statements would be helpful (you can change the names, just keep the structure the same).

yieldsfalsehood Over a year ago

I added an extra example for you.

Collectives™ on Stack Overflow

PostgreSQL foreign key

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related