Thanks a lot
On Fri, Sep 6, 2013 at 9:51 AM, Michael Paquier <michael.paqu...@gmail.com>wrote: > On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe > <alarre...@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 > -- Gracias ----------------- Agustín Larreinegabe
