GeeCON Prague 2014*
The moose likes Oracle/OAS and the fly likes To identify Primary Keys in a table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "To identify Primary Keys in a table" Watch "To identify Primary Keys in a table" New topic
Author

To identify Primary Keys in a table

atish chachan
Greenhorn

Joined: Jul 20, 2006
Posts: 4
hi,
I am new to Oracle.Just started using it.Could anyone help me with this problem. I have many tables in my database. I want to identify the primary keys for a particular table. When i give DESC tablename, it does not show the constraint Primary Key for the table. How do i identify the primary keys for the table.
Shital Supase
Greenhorn

Joined: Jun 05, 2006
Posts: 17
Hello Atish,

The basic aim behind defining the primary key in the table is to identify the record uniquely from ur table. The primary key is a set of one or more number of attributes which when taken collectively will identify the record uniquely from ur table. The syntax for defining a table is

create table <table name> (attribute1 datatype(size),attribute2 datatype(size),.............,primary key(atrributei));

create table students (rno number(3),name varchar2(20),.........................,primary key(rno));


This constraint of primary key imposes the constraints UNIQUE & NOT NULL on ur attribute(s).

Just try it.

If u have any problem u can still ask me / mail me at

sssupase@gmail.com

See u.
atish chachan
Greenhorn

Joined: Jul 20, 2006
Posts: 4
Thanks Shital..

Actually i have the tables already created in the database.

But the Primary Keys have not been defined for the tables.

Is there any way through which i can find out which columns are the primary keys for a table?
Shital Supase
Greenhorn

Joined: Jun 05, 2006
Posts: 17
Hi Atish,

Ya exactly u have a way of defining a primary key after creating a table also. There u need to alter the table, using the command.

syntax:

alter table <table_name> add constraint <constraint_name> primary key(attribute_name);

e.g.:


alter table add constraint pk primary key(rollno);

Giving name to constraint is just for the sake of convinience & u can use it whenever u want to delete that constraint. But remember if u have created a table & inserted the data into the field which u now want to make a primary key then that field should have UNIQUE & NOT NULL values only otherwise it will not alter ur table to define existing attribute as primary key.

Just try it.

It will definately solve ur problem.
Shital Supase
Greenhorn

Joined: Jun 05, 2006
Posts: 17
Now i got ur problem, i think u want to identify the primary key attributes of ur existing tables. OK.

U can just check which attribute(s) is/are defined as primary key of ur table by executing the command

desc <table_name>

it will show u the list of fields, their data types & constraint if any defined. So it will show u the word NOT NULL in front of the primary key field. & if it doesn't show it means u have still not defined primary key for ur table, so now u would have to alter the table.


That's it.
Srinivasa Raghavan
Ranch Hand

Joined: Sep 28, 2004
Posts: 1228
Query "USER_CONSTRAINTS" data dictionary .. You 'll get all the constraints created by you . Then filter the PKY's


Thanks & regards, Srini
MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
Satish Shrikhande
Greenhorn

Joined: Jul 27, 2006
Posts: 1
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7490088329317

Check the link .
For Oracle 9i
select dbms_metadata.get_ddl( 'TABLE', 'EMP' ) from dual;
For Oracle 8 .. you have to use export , import utility to get the structure and its pain in ass .

I suggest you to use PLSQL Developer third party to make your job easy .


Satish Shrikhande <br />MSSQL DBA<br />Verizon<br />Tampa , FL , USA
 
GeeCON Prague 2014
 
subject: To identify Primary Keys in a table