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
26 137693
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'
-
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.
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
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.
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
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.
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
It was quite useful...
Thanks to everyone.
Cheer.
cPiyush.
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.
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
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.
Hi,
I understood the real use of Views. Thanks...
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,
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
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;
-
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.
You had made some basic mistakes
1. Forgotten to add semicolons.
2. Forgotten to add End if.
It is not compulsary to specify type.
If nothing is specified default type Is IN .
Not specifying the parameter MODE is not an error.
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...:-)
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
Here a query which delivers me the TABLE_NAME,COLUMN_NAME to which a foreign key <table>.<column> is pointing (ORACLE 10).
I use the USER system VIEW to get it:
select cc.TABLE_NAME, cc.COLUMN_NAME from USER_CONS_COLUMNS cc WHERE cc.CONSTRAINT_NAME = ( select c.R_CONSTRAINT_NAME from USER_CONS_COLUMNS cc join USER_CONSTRAINTS c on cc.CONSTRAINT_NAME= c.CONSTRAINT_NAME where c.TABLE_NAME = '<table>' and cc.COLUMN_NAME = '<column>' and C.CONSTRAINT_TYPE='R')
Maybe someone knows a shorter query but it works fine for me.
.
again a USER query for ORACLE 10: all columns (query result 'ID') in a <table> that represent foreign keys and point to 'TABLE'.'COLUMN':
select
ccc.COLUMN_NAME as "ID", ucc.TABLE_NAME as "TABLE", ucc.COLUMN_NAME as "COLUMN"
from
USER_CONS_COLUMNS ucc,
(
select
cc.COLUMN_NAME, c.R_CONSTRAINT_NAME
from
USER_CONS_COLUMNS cc
inner join
USER_CONSTRAINTS c
on
cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
where
cc.TABLE_NAME='<table>'
and
c.CONSTRAINT_TYPE='R'
) ccc
where
ucc.CONSTRAINT_NAME = ccc.R_CONSTRAINT_NAME
in order to know the primary key column name and forign key column in a table just execute the query :=
SELECT * FROM ALL_CONS_COLUMNS A JOIN ALL_CONSTRAINTS C ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.TABLE_NAME = <table_name>
AND C.CONSTRAINT_TYPE in ('P','R')
and a.owner=<owner_name>
and c.owner=<owner_name>
Thanks, that was really useful
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Brown |
last post by:
Let's say I have a type hierarchy: (just an example)
the general entity customer:
CREATE TABLE customer(customer_id int, customer_name varchar(250),
customer_type int)
three specific...
|
by: pb648174 |
last post by:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based...
|
by: D. Dante Lorenso |
last post by:
I'd like to run a clean up command on my tables to
eliminate rows that I'm no longer using in the database.
I want to do something like this:
DELETE FROM tablename
WHERE...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: Thomas T. Thai |
last post by:
I'm looking for a better way to make use of foreign keys. Here is a
sample setup:
-- TESTING Foreign Keys
create table mod (
mod_id int not null primary key,
name varchar(32) not null...
|
by: sonal |
last post by:
Hi all,
I hv started with python just recently... and have been assigned to
make an utility which would be used for data validations...
In short we take up various comma separated data files
for...
|
by: ravichoudhari |
last post by:
i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i...
|
by: Wolfgang Keller |
last post by:
Hello,
so far it seems to me as if the only ORM module for Python which
supports composite primary/foreign keys was SQLAlchemy. Which looks a
little bit "overbloated" for my needs: I "just" need...
|
by: JnrJnr |
last post by:
I have two SQL database tables. One contains various products(with unique primary keys) and the other contains information related to the products aswel as the product's foreign keys.
What I want...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |