Query to find Primary and Foreign keys  | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | |
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
|  | Expert | | Join Date: Mar 2007
Posts: 76
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by 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 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'
-
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by 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 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.
|  | Expert | | Join Date: May 2007 Location: Bangalore
Posts: 176
| | | re: Query to find Primary and Foreign keys
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
| | Expert | | Join Date: Mar 2007
Posts: 76
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by 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.
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Hi,
Thanks for all...
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Hi,
Fine. It is working.
What is that table ALL_CONSTRAINTS and ALL_CONS_COLUMNS.
Whether it is System table or anyother?
Thanks,
Srinivasan r
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by rsrinivasan Hi,
Fine. It is working.
What is that table ALL_CONSTRAINTS and ALL_CONS_COLUMNS.
Whether it is System table or anyother?
Thanks,
Srinivasan r These are system views present in SYS schema.
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
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
|  | Newbie | | Join Date: Jan 2007
Posts: 31
| | | re: Query to find Primary and Foreign keys
It was quite useful...
Thanks to everyone.
Cheer.
cPiyush.
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by 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 Views are snapshots of tables. Data is not physically stored in the views. Views reflect the present data in the tables upon which they are created. You can access data from the views in the same way as you access data from tables.
E.g:Say you have a Employee table with the following columns:
Empno Deptno Name Salary
--------- ----------- --------- - --------
You can create a view which contains a subset of these columns. -
CREATE VIEW EMP_VIEW
-
AS
-
SELECT Empno, Name, Salary
-
FROM Employee
-
Now every time you fire a query on the view, the actual query on which the view is built is executed first followed by the query you fired.
A single View can be built upon multiple tables (you can also use JOINs whie creating the views).
I hope this cleared some of your doubts.
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by pradeep kaltari Views are snapshots of tables. Data is not physically stored in the views. Views reflect the present data in the tables upon which they are created. You can access data from the views in the same way as you access data from tables.
E.g:Say you have a Employee table with the following columns:
Empno Deptno Name Salary
--------- ----------- --------- - --------
You can create a view which contains a subset of these columns.
-
CREATE VIEW EMP_VIEW
-
AS
-
SELECT Empno, Name, Salary
-
FROM Employee
-
Now every time you fire a query on the view, the actual query on which the view is built is executed first followed by the query you fired.
A single View can be built upon multiple tables (you can also use JOINs whie creating the views).
I hope this cleared some of your doubts. hi,
Nice. From ur stat I feel that Views are not phisicaly stored in memory. And it is just a Virtual table. Right?
1. Whether it is possible insert or delete records from views?
2. If I insert record in views whether it affects the real table(physical table)?
Why we are using Views instead of Tables directly?
Thanks,
Srinivasan r
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by rsrinivasan hi,
Nice. From ur stat I feel that Views are not phisicaly stored in memory. And it is just a Virtual table. Right?
1. Whether it is possible insert or delete records from views?
2. If I insert record in views whether it affects the real table(physical table)?
Why we are using Views instead of Tables directly?
Thanks,
Srinivasan r Yes you can say views are virtual tables. You cannot insert/delete records from views. To understand the usage of views consider the following:
Suppose 2 ppl (Admin and clerk) access a table. The clerk is authorised only to view (and cant modify the contents of the table) some part of data from the table but the Admin has full access. Now the Admin can create a view containing only the data required for the clerk and allow the clerk to access it.
Also, suppose data is spread out in two tables and you want to see it in a single table. Instead of creating another table to store this data, you can create a view for the same and hence utilize memo in a better way.
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Hi,
I understood the real use of Views. Thanks...
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Hi,
I have another doubt.
How to create user for oracle and assign only some previlliges. For example only to insert recort into the table.
thanks,
Srinivasan r
| | Expert | | Join Date: May 2007 Location: India
Posts: 101
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by rsrinivasan Hi,
I have another doubt.
How to create user for oracle and assign only some previlliges. For example only to insert recort into the table.
thanks,
Srinivasan r Hi,
Follow the link: http://www.techonthenet.com/oracle/grant_revoke.php
Cheers,
Pradeep
| | Familiar Sight | | Join Date: Mar 2007 Location: India
Posts: 221
| | | re: Query to find Primary and Foreign keys
Hi,
I create a function in oracle -
create or replace function
-
divide(first integer, second integer) return integer is
-
begin
-
if first>second
-
return first
-
else
-
return second
-
end;
-
But it tells warning as below..
Warning: Function created with compilation errors.
What is the error? Give correct syntex...
Thanks,
Srinivasan r
|  | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,509
| | | re: Query to find Primary and Foreign keys
check the code -
create or replace function divide(first integer, second integer) return integer is
-
begin
-
if first > second then return first;
-
else return second;
-
end if;
-
end;
-
| | Newbie | | Join Date: Jan 2007
Posts: 31
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by rsrinivasan Hi,
I create a function in oracle
-
create or replace function
-
divide(first integer, second integer) return integer is
-
begin
-
if first>second
-
return first
-
else
-
return second
-
end;
-
But it tells warning as below..
Warning: Function created with compilation errors.
What is the error? Give correct syntex...
Thanks,
Srinivasan r Execute this query if you are getting compilation errors:- show errors;
This will list the line number/col number combination & the description of error which you have made in the function definition.
Check your mistakes using this, as I can see one mistake just by reviewing ur code that you have not specifieds the type(IN/OUT) of the arguments passed in the prototype...
Cheer!!!
cPiyush.
| | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,509
| | | re: Query to find Primary and Foreign keys
You had made some basic mistakes
1. Forgotten to add semicolons.
2. Forgotten to add End if.
| | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,509
| | | re: Query to find Primary and Foreign keys
It is not compulsary to specify type.
If nothing is specified default type Is IN .
Not specifying the parameter MODE is not an error.
| | Newbie | | Join Date: Jan 2007
Posts: 31
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by debasisdas It is not compulsary to specify type.
If nothing is specified default type Is IN .
Not specifying the parameter MODE is not an error. Oh...Thanks Das...:-)
| | Newbie | | Join Date: Sep 2007
Posts: 6
| | | re: Query to find Primary and Foreign keys
Quote:
Originally Posted by pradeep kaltari 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. Hi all,
the disscusion is very interesting... I would need to know the way to get the FK between two tables, could you help me?
Thank you in advance,
Pumuky
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,467 network members.
|
