Re: How to find out which column has the primary key?

From: Andrew Tompkins <andrewto_at_kingfish.cse.tek.com>
Date: 1997/05/09
Message-ID: <ANDREWTO.97May9155059@kingfish.cse.tek.com>#1/1

In article <5kuh4p$lg9$1_at_NNTP.MsState.Edu>   aak2_at_Ra.MsState.Edu (Atif Ahmad Khan) writes:

> I am trying to find out which column in a table has the primary key.
> describe tablename doesn't show it. Any ideas?
>
> Thanks.
>
> Atif Khan
> aak2_at_ra.msstate.edu

Try this:

SELECT column_name, position, constraint_name from user_cons_columns   WHERE ( table_name = <table-name> ) AND     ( constraint_name IN (

      SELECT constraint_name FROM user_constraints
        WHERE ( table_name = <table-name> ) AND ( constraint_type = 'P' )

  ORDER BY constraint_name, position;

where <table_name> is in all caps (the select is case sensitive).

The internal select pulls up the primary key's constraint name for the table. The outer one gets the column names and position.

--Andy
-- 
-------------------------------------------------------
| Andrew G. Tompkins    | #include <disclaimer.std.h> |
| Software Design Eng.  ------------------------------|
| Tektronix, Inc.                                     |
| Phone: (503) 627-5172      fax: (503) 627-5548      |
| email: Andrew.Tompkins_at_tek.com (work)               |
|        andytom_at_teleport.com (home)                  |
| http://www.teleport.com/~andytom/                   |
-------------------------------------------------------

Received on Fri May 09 1997 - 00:00:00 CDT