To identify Primary Keys in a table

[atish chachan]

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]

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

[email protected]

See u.

[atish chachan]

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]

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]

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]

Query "USER_CONSTRAINTS" data dictionary .. You 'll get all the constraints created by you . Then filter the PKY's

[Satish Shrikhande]

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 .