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