Re: PK referenced function

Thanks a lot

On Fri, Sep 6, 2013 at 9:51 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>wrote:

> On Fri, Sep 6, 2013 at 10:26 PM, Agustin Larreinegabe
> <alarreine(at)gmail(dot)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