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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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..

please let me know..

thanks in advance,
ramesh

On Tue, Jul 22, 2014 at 7:52 AM, Adrian Klaver <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..

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

---------- Forwarded message ----------
From: Ramesh T <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>

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 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>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general