Thread: Need r_constraint_name

On 07/19/2014 12:26 PM, Ramesh T wrote:
> Hi,
>             In oracle got constraint details using user_constraint,
>
> But in postgres how to get the r_constraint_name,constraint_name  of the
> particular table...?
>
> mainly i need r_constraint_name on table.. how to get it?please let me know
>
>

 From psql:

test=> CREATE TABLE parent_tbl(id serial primary key, fld_1 text);
NOTICE:  CREATE TABLE will create implicit sequence "parent_tbl_id_seq"
for serial column "parent_tbl.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"parent_tbl_pkey" for table "parent_tbl"
CREATE TABLE

test=> CREATE TABLE child_tbl (id serial primary key, fk_fld integer
references parent_tbl, fld_2 text);
NOTICE:  CREATE TABLE will create implicit sequence "child_tbl_id_seq"
for serial column "child_tbl.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"child_tbl_pkey" for table "child_tbl"
CREATE TABLE

test=> \d parent_tbl
                          Table "public.parent_tbl"
  Column |  Type   |                        Modifiers

--------+---------+---------------------------------------------------------
  id     | integer | not null default nextval('parent_tbl_id_seq'::regclass)
  fld_1  | text    |
Indexes:
     "parent_tbl_pkey" PRIMARY KEY, btree (id)
Referenced by:
     TABLE "child_tbl" CONSTRAINT "child_tbl_fk_fld_fkey" FOREIGN KEY
(fk_fld) REFERENCES parent_tbl(id)

test=> \d child_tbl
                          Table "public.child_tbl"
  Column |  Type   |                       Modifiers
--------+---------+--------------------------------------------------------
  id     | integer | not null default nextval('child_tbl_id_seq'::regclass)
  fk_fld | integer |
  fld_2  | text    |
Indexes:
     "child_tbl_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
     "child_tbl_fk_fld_fkey" FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)


If you want to know what query psql uses to get this information start
psql with -E, this will tell you that the queries are:


To get the child key that references the parent from the parent:

test=> 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 = 'parent_tbl'::regclass AND c.contype = 'f' ORDER BY 1
;

         conname        | conrelid  |                     condef

-----------------------+-----------+------------------------------------------------
  child_tbl_fk_fld_fkey | child_tbl | FOREIGN KEY (fk_fld) REFERENCES
parent_tbl(id)


To get the information from the child table:

test=> SELECT conname,
   pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = 'child_tbl'::regclass AND r.contype = 'f' ORDER BY 1
;
         conname        |                     condef
-----------------------+------------------------------------------------
  child_tbl_fk_fld_fkey | FOREIGN KEY (fk_fld) REFERENCES parent_tbl(id)


I used the regclass cast to convert the table names to the appropriate
ids the query expects. In the psql output you will see the numbers.


>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/22/2014 03:12 AM, Ramesh T wrote:
> thank u ,
>
> SELECT constraint_name
>                  FROM information_schema.table_constraints AS tc
>         WHERE     tc.table_name = p_table_name
>               AND constraint_name IN (SELECT constraint_name
>                                         FROM
> information_schema.table_constraints AS tc
>                                        WHERE     tc.table_name =
>                                                     p_ref_table_name
>                                              AND tc.constraint_type =
>                                                     'PRIMARY KEY');
>
> is this correct process same as above ..
>
> but i want check "r_constraint_name" instead of "constraint_name"  in
> outer statement in above code..

I am not sure you are going to find that column. I am not an Oracle user
but I did find this:

http://docs.oracle.com/html/B13531_01/ap_d.htm

R_CONSTRAINT_NAME is the name of the unique constraint definition for
the referenced table.

So it would seem r_constraint_name is an column name in an Oracle system
view. I know of no such name in the Postgres system catalog. I am sure
the same information is available, you are just going to have to be
specific about what you are looking for. From the above that would seem
to be the name of the unique key that a foreign key references.

Is that correct?

If so the query you show above will not work as a UNIQUE key does not
necessarily have to be the PRIMARY KEY.

>
> please let me know..
>
> thanks in advance,
> ramesh
>

--
Adrian Klaver
adrian.klaver@aklaver.com

On 07/22/2014 07:21 AM, Ramesh T wrote:
>
>
> ---------- Forwarded message ----------
> From: *Ramesh T* <rameshparnanditech@gmail.com
> <mailto:rameshparnanditech@gmail.com>>
> Date: Tue, Jul 22, 2014 at 7:50 PM
> Subject: Re: [GENERAL] Need r_constraint_name
> To: Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
>
>
> Just i'm retriving the constraint_name when  i enter child_table_name
> for inner query and that constraint name is checking
> parent_table on outer statement that constraint_name is equal then
> display the constraint name ..?but outer select is r_constraint_name

I still am not sure I am following.

If the inner query is on the child table, why are you selecting for
'PRIMARY KEY' and not 'FOREIGN KEY'?

That would sort of make sense if you where supplying the parent table
name, but again a FOREIGN KEY references a UNIQUE constraint, which may
or may not be a PRIMARY KEY. So restricting to a PRIMARY KEY will cause
you to miss relationships.

What exactly are you looking for?

>
> i think in postgres r_constraint_name is also include in the
> pg_constraints details not a seperate column in postgres for that ,if
> parent table have consraint_name same as the child table return from
> inner query that constraint_name displayed out..
>
>   my assumption..is it corect?
> from last query..
>
>
> thanks in advance..
> ramesh
>
>
> On Tue, Jul 22, 2014 at 7:18 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 07/22/2014 03:12 AM, Ramesh T wrote:
>
>         thank u ,
>
>         SELECT constraint_name
>                           FROM information_schema.table___constraints AS tc
>                  WHERE     tc.table_name = p_table_name
>                        AND constraint_name IN (SELECT constraint_name
>                                                  FROM
>         information_schema.table___constraints AS tc
>                                                 WHERE     tc.table_name =
>
>         p_ref_table_name
>                                                       AND
>         tc.constraint_type =
>                                                              'PRIMARY KEY');
>
>         is this correct process same as above ..
>
>         but i want check "r_constraint_name" instead of
>         "constraint_name"  in
>         outer statement in above code..
>


--
Adrian Klaver
adrian.klaver@aklaver.com