Re: PK referenced function

On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
<alarreine@gmail.com> wrote:
> I want to know if exists a postgres function or some easy way to know if a
> PK in a table is already referenced in another table/tables.
psql has all you want for that. For example in this case:
=# create table aa (a int primary key);
CREATE TABLE
=# create table bb (a int references aa);
CREATE TABLE
=# create table cc (a int references aa);
CREATE TABLE
=# \d aa
      Table "public.aa"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "aa_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "bb" CONSTRAINT "bb_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
    TABLE "cc" CONSTRAINT "cc_a_fkey" FOREIGN KEY (a) REFERENCES aa(a)
Running a simple ¥d on the relation having the primary key also lists
where is is referenced...

Now by using psql -E you can output as well the queries used by psql
to fetch this information from server, and in your case here is how to
get the foreign keys referencing it:
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '$RELATION_NAME'::regclass AND c.contype = 'f' ORDER BY 1;
Simply replace RELATION_NAME by what you want.

> e.g.
> I want to delete a row but first I've got to change or delete in the
> table/tables where is referenced, and I have many table where could be
> referenced.
Do that with ON DELETE/UPDATE CASCADE when defining a foreign key.
Here is an example with ON DELETE CASCADE:
=# create table aa (a int primary key);
CREATE TABLE
=# create table dd (a int references aa on delete cascade);
CREATE TABLE
=# insert into aa values (1);
INSERT 0 1
=# insert into dd values (1);
INSERT 0 1
=# delete from aa where a = 1;
DELETE 1
=# select * from dd;
 a
---
(0 rows)
Documentation is here for reference:
http://www.postgresql.org/docs/9.2/static/ddl-constraints.html.
--
Michael