Query to find Primary and Foreign keys

[rsrinivasan]

Hi,

1. I have some table. But i do not know how to find primary key column, forign key column in that table. Is any procedure or function is available in oracle to find this?.

Thanks,
Srinivasan r

[chandu031]

Hi,

1. I have some table. But i do not know how to find primary key column, forign key column in that table. Is any procedure or function is available in oracle to find this?.

Thanks,
Srinivasan r

Hi,

This is the query to get a list of primary keys:

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'
 

And this is the query for foreign keys:

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'
 

[pradeep kaltari]

Hi,

1. I have some table. But i do not know how to find primary key column, forign key column in that table. Is any procedure or function is available in oracle to find this?.

Thanks,
Srinivasan r

Hi,
The following query will give all the constraints specified on a table belonging to <owner_name> schema.

SELECT * 
FROM SYS.ALL_CONSTRAINTS A , SYS.ALL_CONS_COLUMNS B
WHERE S.OWNER=<owner_name> AND S.TABLE_NAME=<table_name> AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
 

You can further filter this based upon the constraint type you are interested in. For primary key constraint, include CONSTRAINT_TYPE ='P' in the WHERE clause. For foreign key constraint, include CONSTRAINT_TYPE ='R' in the WHERE clause.

[frozenmist]

Hi ,

To correct Chandu. you should also have the owner name.
So code becomes

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R' and C.OWNER='<OWNER NAME>'
 

Cheers

[chandu031]

Hi,

This is the query to get a list of primary keys:

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'
 

And this is the query for foreign keys:

SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'
 

Yes..You will have to add one more filter on the OWNER as the same table names can be used across users. Thks for pointing it out Frozen.

If you want only the column names you can use
SELECT C.COLUMN_NAME, C.POSITION instead of SELECT *

Position is helpful when you have composite keys.

[rsrinivasan]

Hi,
Thanks for all...

[rsrinivasan]

Hi,

Fine. It is working.
What is that table ALL_CONSTRAINTS and ALL_CONS_COLUMN S.

Whether it is System table or anyother?

Thanks,
Srinivasan r

[pradeep kaltari]

Hi,

Fine. It is working.
What is that table ALL_CONSTRAINTS and ALL_CONS_COLUMN S.

Whether it is System table or anyother?

Thanks,
Srinivasan r

These are system views present in SYS schema.

[rsrinivasan]

Hi,
Just now i see it..

What is the difference between Views and Tables. Anything advantage by using Views? When should we use Views?

Thanks..
Srinivasan r

[cpiyush]

It was quite useful...

Thanks to everyone.

Cheer.
cPiyush.