Gossamer Forum
Home : General : Databases and SQL :

How to get primary key for table in Oracle?

Quote Reply
How to get primary key for table in Oracle?
Is there a simple way to get (in perl) the name of the primary key column in an Oracle table?
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
I'm not too familiar with Oracle, so sorry if I'm off base but...

I don't believe you can fetch it from the $sth value, especially as primary keys can actually be composed of more than one column.

If there is a query in sql that will allow you to fetch the primary key from a table, you can run the query and fetch the primary key name(s).
Quote Reply
Re: [Aki] How to get primary key for table in Oracle? In reply to
Ok, then let me rephrase: what would be an sql query to fetch the primary key of a table, given that the table has only one primary key?
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Hello Kellner,

There is a perl file named "tabinfo.pl" which comes with DBD-Oracle-1.12 in the Oracle.ex

folder.

This may be an example of what you are looking for.

Thanks

kode

see DBD-Oracle-1.12

Last edited by:

kode: Sep 6, 2002, 9:06 AM
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
No idea how Oracle works but the sql query I know of is:

SHOW KEYS FROM Table
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
One suggestion...use a GUI tool, like Toad, which will allow you to see table configurations, including sequences, triggers, constraints, etc.

You can purchase Toad from Quest Software. It's a great program.

Alternatively, you could purchase ERWIN or ERStudio to reverse engineer your database and view its schema, including PK, FK, constraints, etc.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] How to get primary key for table in Oracle? In reply to
Thanks for all the replies so far.

I'm not aware of how Oracle handles "show"-commands, but will follow up on this.

I'm not trying to get table info in general, so I don't need a GUI. Actually, I'm trying to code a generic routine in a perl script which, when the name of a table is passed to it, retrieves the name of the primary key column and then associates other column values with the value of the primary key in each row. I could do this differently, but just thought it would be a good idea to have a routine which gets me the name of the primary key column.

The file tabinfo.pl which comes with DBI retrieves all sorts of table information, but not the primary key. It uses the type_info method from DBI, which, if I understand the DBI documentation correctly, is (a) experimental and (b) doesn't get you the attribute "is primary key".

I think there's a system table called usr_tab_columns or such, and it might be possible to retrieve primary key information from there, but I don't know enough about Oracle or SQL to do this.
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Or querying the table sequence.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] How to get primary key for table in Oracle? In reply to
I had posted this question to dbi-users as well (though it wasn't a dbi question, really), and now found a solution that works:

my $sql = qq|select a.column_name from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and a.table_name = b.table_name and b.table_name = 'TABLE_NAME' and b.constraint_type = 'P'|;

$sth = $dbh->prepare($sql);
$sth->execute();
while (my $hash_ref = $sth->fetchrow_hashref) {
my $key = $hash_ref->{COLUMN_NAME};
print "$key"; # yep: the primary key name!

}

(never mind the clumsy hashref, it's the rest that counts.)



Cheers,
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
All that just to get a Primary key?

Wow... *stunned* that's way more work than I anticipated, the query is huge!
Quote Reply
Re: [Aki] How to get primary key for table in Oracle? In reply to
Well, I like to kill my ants with 2-ton-weights Monty-Python-style :)
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Just out of interest did you try the SHOW query?
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Hello kellner,

You might try:

select constraint_name, constraint_type, table_name from user_constraints

Thanks

Kode


Attached a Sql Plus text file for all info, that may help you.

Last edited by:

kode: Sep 6, 2002, 11:35 AM
Quote Reply
Re: [Paul] How to get primary key for table in Oracle? In reply to
Paul,

That doesn't work in Oracle.

You'll get the following error message:

Quote:

The following error has occurred:

ORA-00900: invalid SQL statement


ORA-00900

Quote:
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is
not installed and a SQL statement is issued that requires this option (for example, a CREATE PROCEDURE statement). You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.

Action: Correct the syntax or install the Procedural Option.

Reference: http://download-west.oracle.com/....901/a90202/e900.htm
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [kode] How to get primary key for table in Oracle? In reply to
I tried the show-query from the command-line in sqlplus, and it didn't work; I was too lazy to try it from a perl-script.

The problem with the query you gave me, kode, is that constraint_name is *not* the name of a column, and that I need the name of the column to which the primary key constraint is attached. user_constraints simply doesn't contain the column name.

I don't think there's a way around a combined table query.
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Hello Kellner, Sorry , that was an incorrect select statement for Oracle version X?.

I edited the previous post and attached a Sql Plus Oracle script, it may work depending

on the version of Oracle you are using.

Thanks

kode
Quote Reply
Re: [kode] How to get primary key for table in Oracle? In reply to
Well, the command "select constraint_name from user_constraints where table_name='TABLE_NAME' and constraint_type='P'" does return a result, but as I've said before, the column "constraint_name" does not store the column name of the table I'm interested in, and I *want* the column name.

(This is on Oracle 8.0.6)
kellner
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
Try the SQL command below:

SELECT COL.CONSTRAINT_NAME,COL.COLUMN_NAME,CON.CONSTRAINT_TYPE
FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS CON
WHERE COL.TABLE_NAME = upper('TABLE_NAME')
AND COL.TABLE_NAME = CON.TABLE_NAME
AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE='P';

It will be shown the primary key of a table.

TheStone.

B.
Quote Reply
Re: [TheStone] How to get primary key for table in Oracle? In reply to
Hi Folks,

for me (I am working with SQL under DB2) nothing of the above worked!
....then I found out the following:

SELECT colname FROM syscat.columns WHERE tabname='MY_TABLE_NAME' AND keyseq IS NOT NULL

This returns all the column names (none, one or more) that belong to the primary key of the table.

NOTE: The name of the table must be in UPPER CASE letters and in quotes!
NOTE: The value of 'keyseq (SMALLINT)' is documented as follows:
"The column's numerical position within the table's primary key. This field is null for subtables and hierarchy tables."

To play around a bit more, try the following queries:
SELECT tabname, colname, keyseq FROM syscat.columns WHERE tabname='MY_TABLE_NAME'
==> show all column-names and the keyseq-values
SELECT tabname, colname, keyseq FROM syscat.columns WHERE keyseq IS NOT NULL
==> show the primary-key-columns of ALL the tables


Have fun! (...and don't catch a cold outside!)
Quote Reply
Re: [kellner] How to get primary key for table in Oracle? In reply to
This Is Possible If You Create a View On Some Tables In Data Dictionary Of Oracle(This May Req Ur DBA Permission) If He Permits Ur Problem Can Be Solved

Oracle Data Dict Stores All Information :)

Thnx


=_= =_= =_= =_= =_= =_=


You can chain me, you can torture me, you can even destroy this body, but you will never imprison my mind.
-Mahatma Gandhi
Quote Reply
How to get primary key for table in Oracle? In reply to
hi,
A single query to get the primary keys for all the tables in the database in SQL is as follows

SELECT * FROM sysobjects WHERE type = 'PK'

hope this helps :)
Quote Reply
Re: [sunita_gateway] How to get primary key for table in Oracle? In reply to
try this:

select dbms_metadata.get_ddl('TABLE', '$TABLE', '$THEME') from dual;

this will display the command used to create the table and it should contain the primary key you are looking for.
Quote Reply
Re: [hongtao] How to get primary key for table in Oracle? In reply to
This thread was started 6 years ago, and last reply was 4 years ago =)

----
Cheers,

Dan
Founder and CEO

LionsGate Creative
GoodPassRobot
Magelln