CODEWITHSUNDEEP
sqldatabasepostgresqlforeign-keysrelational-database
Stefan Sprenger
Stefan Sprenger

Reputation: 1080

PostgreSQL foreign key

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)
);

Upvotes: 0

Views: 955

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

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

Upvotes: 2

Related Questions