Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

To identify Primary Keys in a table

 
atish chachan
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Query "USER_CONSTRAINTS" data dictionary .. You 'll get all the constraints created by you . Then filter the PKY's
 
Satish Shrikhande
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 .
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic