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
