f



Get the primary key name of a table

Hello there!

Which SELECT statement do I need to execute to get the primary key name of a
certain table?

Robert


0
Robert
3/16/2005 4:09:52 PM
comp.databases.oracle.misc 8436 articles. 1 followers. Post Follow

5 Replies
647 Views

Similar Articles

[PageSpeed] 8

Robert Wehofer wrote:
> Hello there!
>
> Which SELECT statement do I need to execute to get the primary key
name of a
> certain table?
>
> Robert

Goto http://tahiti.oracle.com and lookup description for
user_constraints, all_constraints and dba_constraints.

Regards
/Rauf

0
Rauf
3/16/2005 4:18:12 PM
On Wed, 16 Mar 2005 16:09:52 +0000, Robert Wehofer wrote:

> Hello there!
> 
> Which SELECT statement do I need to execute to get the primary key name of a
> certain table?
> 
> Robert

Go to the doc at http://docs.oracle.com or http://tahiti.oracle.com, dive
into the version you need - these things can change based on version
(although this one doesn't) - and pull up the REFERENCE manual.  Take a
gander at the ???_CONSTRAINTS and ???_CONS_COLUMNS views (??? is one
of USER, ALL, or DBA depending on you privs).

I'll let you figger the actual SELECT based on this.
0
GreyBeard
3/16/2005 4:20:15 PM
"Robert Wehofer" <thalion77@graffiti.net> wrote in message 
news:kTYZd.2086$zY6.461@news.chello.at...
> Hello there!
>
> Which SELECT statement do I need to execute to get the primary key name of 
> a
> certain table?

Hi Robert

you've asked a couple of data dictionary type questions now. I suggest that 
you have a look at the views named

USER_XXX
ALL_XXX
DBA_XXX

Which describe the data dictionary in somewhat exhaustive detail. The 
concepts manual is pretty good too.


-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com 


0
Niall
3/16/2005 8:30:47 PM
Hello!

Thank you for your advices.

I now use following statement to get the primary key of a table:

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

I hope this statement works in all oracle versions.

Robert


0
Robert
3/17/2005 7:44:14 AM
On Thu, 17 Mar 2005 07:44:14 GMT, "Robert Wehofer"
<thalion77@graffiti.net> wrote:

>
>Hello!
>
>Thank you for your advices.
>
>I now use following statement to get the primary key of a table:
>
>SELECT COL.COLUMN_NAME FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS CON
>WHERE COL.TABLE_NAME = 'TABLENAME' AND COL.TABLE_NAME = CON.TABLE_NAME
>AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE='P';
>
>I hope this statement works in all oracle versions.
>
>Robert
>

Apart from the fact it should be con.table_name = '<table name>' and
col.table_name = con.table_name is redundant, this statement will work
from Oracle 6.0 onwards


--
Sybrand Bakker, Senior Oracle DBA
0
Sybrand
3/17/2005 8:04:23 AM
Reply:

Similar Artilces:

Get all child table and key names of a parent table
select stab.tabname Parent, scol.colname Primary_key, sstab.tabname Child, sscol.colname Child_key from syscolumns scol, syscolumns sscol, sysindexes sind, sysindexes ssind, sysconstraints scon, sysconstraints sscon, systables stab, systables sstab, sysreferences sref where scol.tabid=sind.tabid and scol.colno = sind.part1 and sind.idxname=scon.idxname and stab.tabid=scon.tabid and sstab.tabid=sscon.tabid and sscol.tabid = ssind.tabid and (sscol.colno = ssind.part1 or sscol.coln...

Get all primary keys in database.
Hi, I am writing a code to retrieve all the primary keys of the tables in the database. I tried to use DatabaseMetadata dbmd = dbConnection.getMetaData(); ResultSet rs = dbmd.getprimarykeys(null,null ,"%"), I do not get the result set in my vector which i created to store the primarykeys. But when i explicitly write the name of the table like ResultSet rs = dbmd.getprimarykeys(null,null, "movies"), i get the primary key. DO someone know why i have this problem? tolu45 schrieb: > Hi, I am writing a code to retrieve all the primary keys of the tables > in the databas...

Linking an Oracle table to MS Access via ODBC: Access assigns wrong primary key to linked table
Hello, I'm using Access to link to tables of a calculation tool that's been built = on a Oracle back end. I recently updated that tool, and now it seems something has been changed i= n Oracle that causes Access to automatically assign the wrong primary keys = to certain tables. This happens when I update the tables using the connecti= vity wizard or when re-linking the tables. Is there a way to prevent Acces to automatically put keys to the Oracle tab= les I'm trying to link using ODBC? If I'm linking MySQL tables the ODBC con= nection wizard of Access always asks ...

Linking an Oracle table to MS Access via ODBC: Access assigns wrong primary key to linked table
Hello, I'm using Access to link to tables of a calculation tool that's been built = on a Oracle back end. I recently updated that tool, and now it seems something has been changed i= n Oracle that causes Access to automatically assign the wrong primary keys = to certain tables. This happens when I update the tables using the connecti= vity wizard or when re-linking the tables. Is there a way to prevent Acces to automatically put keys to the Oracle tab= les I'm trying to link using ODBC? If I'm linking MySQL tables the ODBC con= nection wizard of Access always asks ...

I am trying to set up a database using the database connectivity toolkit and 8.5. I have tables setup and each one has an autoincrementing primary ID key.
I have very little experience with databases, but I have to set one up. I am setting up a relational BD, I have several tables set up, each has a primary key set up as a ID, autoincrement. &nbsp; How can I not send this column data, and let it autoincrement like it should so that it is a unique entry. I keep on getting an error that says that the number of colums don't match. Is there any way to do this. &nbsp; I want the database to keep track of the line count for this, and increment when each new entry arrives.&nbsp; Does this make any sense? The insert data VI has a Colu...

find primary key with table name
Hi, I need to translate the T-SQL code : SELECT @PK_NAME = so.name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = @TABLE AND so.xtype = 'PK' -- Si on a trouv� la PK on la supprime IF @PK_NAME IS NOT NULL EXECUTE('alter table [' + @TABLE + '] DROP CONSTRAINT ' + @PK_NAME) ; in PL-SQL How to find the primary key for a table name with PL-SQL and delete it ? Thanks and happy new year ! Jean-Luc ! -- Jean-Luc M. On Jan 2, 10:32 am, Jean-Luc M. <alphom...@free.fr> wrote: > Hi, > > I need to translate the T-SQL code : > > SELECT > @PK_NAME =3D so.name > FROM > sysobjects so JOIN sysconstraints sc ON so.id =3D sc.constid > WHERE > object_name(so.parent_obj) =3D @TABLE AND so.xtype =3D 'PK' > > -- Si on a trouv=E9 la PK on la supprime > IF @PK_NAME IS NOT NULL > EXECUTE('alter table [' + @TABLE + '] DROP CONSTRAINT ' + @PK_NAME) > ; > > in PL-SQL > > How to find the primary key for a table name with PL-SQL and delete it > ? > > Thanks and happy new year ! > > Jean-Luc ! > > -- > Jean-Luc M. alter table <table_name> drop primary key No need to search the name. Oracle !=3D sqlserver NEVER EVER try to port sqlserver code to PL/SQL. It won't work and it won't scale. Please ALWAYS read the...

Getting the database name of a given table?
Hi All. If I have a piece of SQL like:- SELECT some_cols FROM DB1:atable UNION SELECT some_cols FROM DB2:atable UNION SELECT some_cols FROM DB3:atable How can I also return as a column the database the row matches so I can differentiate between DB1, DB2 and DB3 in the above example? I've tried a few things but no success so far. Any inspiration appreciated! TIA. David ...

BUG #1055: no keys in inherited table with primary key when inserting into inheriting table
The following bug has been logged online: Bug reference: 1055 Logged by: Agri Email address: agri@desnol.ru PostgreSQL version: 7.4 Operating system: PC-linux-gnu Description: no keys in inherited table with primary key when inserting into inheriting table Details: let me desribe a bug in the term of sql commands: create table first (id int primary key ); create table second (f2 int) inherits (first); create table third (ref_id int); alter table third add constraint third_ref_first foreign key (ref_id) references first; insert int...

Is Primary Key always the index of the table in Oracle?
Can someone explain to me if the Primary Key always the same as Index of the table in Oracle? If not, how are they related? Thank you in advance! On Mar 6, 10:22 am, "Kaka" <kaka....@gmail.com> wrote: > Can someone explain to me if the Primary Key always the same as Index > of the table in Oracle? If not, how are they related? Thank you in > advance! I don't think that primary Key is same as index only. Infact primary key apply a index on column as well as it applies not null and unique constraint and can be use to bind table with another table using foreign...

SQL:- Query to get that primary key of the table?
Iam working in IBM Mainframe. Please advice me. I have 3 jobs in my PDS.The sucessful execution of first job, should trigger thesecond one , and the sucessfull execution of second job , should trigger third. How will i implement this scenario? Query to get that primary key of the table? Thanks.regards, john. In article <f4c71f2.0411091110.369e1216@posting.google.com>, John Varughese <johnchittazhath@yahoo.com> wrote: >Iam working in IBM Mainframe. Please advice me. > >I have 3 jobs in my PDS.The sucessful execution of first job, should >trigger thesecond one , and...

search the primary key given the table name
Hi all, How can get the primary key string from the given table name? i know it should from system tables of "sysobjects, syscolumns, and sysconstraints", but when i execute the statement like that: select a.name from syscolumns a,sysobjects b,sysconstraints c where a.id = b.id and b.name ='Agreement' and a.id = c.id and a.colid = c.colid and c.status = 1 i can't get the primary key out, what the trick here? bye the sql help file, 'status' in sysconstraints table: 1 = PRIMARY KEY constraint. 2 = UNIQUE KEY constraint. what is exact value refers to PRIMARY KEY...

Illegal characters in database names, table names, user names...
I'm developing a simple proof-of-concept Web application, more as a personal programming exercise than anything else, that presents the user with a login form where they can type in a database name, username, and password. I then present them with a text field to type in SQL queries, and hand back a nicely-formatted HTML table with the result of their queries. Basically, just a programming exercise to get my feet wet with the Web application framework I'm using (a Python-based framework called Quixote). As I was writing the database-connection code, I got to thinking about security. How do I prevent the user from entering something like "eviluser ; drop database template1" in the username field? One way to go about it, I thought, would be to examine the dbname, username, and/or password fields and make sure that they contain only legal characters. But I couldn't find a reference in the PostgreSQL documentation to tell me which characters are considered legal or illegal in database names, usernames, or table names. And what about passwords? There may be all sorts of punctuation in there. Is there a list of illegal characters somewhere? What other methods would you recommend to validate user input before I send it off to PostgreSQL? -- Robin Munn rmunn@pobox.com ...

Problem getting the GUI to import and save a table to a specific name so that my program can call the same name for any imported table
Hi everyone, I have a problem about loading a table that the user has picked. Basically, my GUI has an option to input a table of data to be used in my program. I initially thought that I could get the user to enter the address of the table and then I would use the following code in my program: > tabaddress = get(handle.tabaddress,'String') > table = importdata(tabaddress) I don't know why this wont work? because I have made sure that tabaddress is stored by the user, to start and end with an inverted comma. I then thought perhaps it would be better to just have a uiimpor...

How can I get the Primary Key field's name or the list of Pkeys
Hi, This code will get me all the table names from an ADO connection: method Get_User_Table_Name_List() class Ado_Connection local aFields as array local aTables as array local oRSt as AdoRecordSet local nPos as dword oRst := self:OpenSchema(adSchemaTables,nil,nil) aFields := oRst:Fields:AsArray() nPos := AScan(aFields,{|oField|oField:Name == 'TABLE_NAME'}) aTables := {} do while .not. oRst:EOF if (Upper(AllTrim(oRst:[Collect, 4])) == "TABLE") AAdd(aTables,oRst:[Collect, nPos] ) endif oRst:MoveNext() ...

Get all table names with a given column name
All, is there a way of getting all table names that contain a column name? I'm looking at a DB that has 125+ tables and I'm interested in finding all table names that contain the column order_date. How can I do it? TIA, Sashi Select table_name from information_Schema.columns where column_name='column name' Madhivanan Sashi wrote: > All, is there a way of getting all table names that contain a column > name? > I'm looking at a DB that has 125+ tables and I'm interested in finding > all table names that contain the column order_date. > How can I do it...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > &...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, ...

Primary Key on a table is not coming over when I import table in SSIS
SQL 2005 SP2. When I try to import a table from one server to another using SSIS, the table imported on the new server does not have the primary key which exists on the source table. Help please. Thanks. Raziq. *** Sent via Developersdex http://www.developersdex.com *** There is a setting under the options to move it. Be sure all objects are checked within the options that you need. By default I dont not believe this is the case. i would bet any indexes and any foreign keys besides primary keys did not move over as well as this is also an option. Mike On Jul 21, 3:37=A0pm, Raziq Shekha...

How do I find the primary key of a table by querying the system tables?
Hi all, I've googled for this for a while but haven't found anything. Please could someone tell me how I can find iF a table has a primary key and what is the name of the constraint. I have this so far (I'm on ASE 11.0 for Linux btw): select i.name, o.name from sysindexes i, sysobjects o where i.id = o.id and o.type = 'U'; I think all my primary key indexes start PK_, but I'm not sure. I figure the primary key must be flagged somewhere, but I can't see anything obvious on the system tables. TIA Rich Hi Richard, Try the "status" co...

Unknown primary keys and foreign keys ina database
I would like to connect to a database and determine, on the fly, what columns are the foreign and primary keys for each table.&nbsp; Is there any way to do this using the LabView Database connectivity toolkit or a SQL query? Thank you! Brad Found it, thanks!&nbsp; For future reference (for anyone who might search for this topic) Go to: Tools -&gt; Options -&gt; View and check "system" and "hidden" objects to explore the structure of these tables.&nbsp; It's not recommended to edit them directly for obvious reasons, but will at least give an idea...

Two foreign keys pointing to the same primary key in other table
i came accross requirement of multiple foreign keys in a table referencing the same primary key another table. i created the table relations using the relations editor in access. even though i could have multiple foreign keys to a table access did allow the referential integrity. my database structure is table1 - students table table 2 - subjects table table 3 - subjects opted by students in the table 3 totally there are 6 subject columns and a student can opt for variable number of subjects. table structure students ID -> primary key, FirstName, LastName, ........ subjects ID->pri...

Primary Key built from other Primary Keys?
Hello comp.databases, Can a primary key of a table be built from primary keys of other tables? For example, I have a table called 'Match' and the way I'd like to construct it is that the primary key of this table is built from 'Home Team', 'Opposition', 'Location' (these are all entities with tables) and 'Date' (this is an attribute of the 'Match' table). For your information, the 'Home Team' table has 'Name' for it's primary key and 'Opposition' and 'Location' both have 'Name' and 'Z...

Relationships error, C# Visual Studio 2005 database bug?, "the columns in table XYZ do not match an existing primary key or UNIQUE constraint", copying columns
Problem: I replicated, using Frasier "Pro Visual C++/CLI" (2006), Chap 12, a database that has a relationship between two tables, called Content and Author, using a common column, called "AuthorID". I used the Server Explorer insider Visual Studio 2005 to do this. It worked. Now I tried the exact same thing from Visual Studio 2005 but this time using C# not C++ as my language of choice. The interface is slightly different, but I made sure everything was done as before. I used the Server Explorer 'GUI' to do this, clicking and following the 'wizards' as...

Mysql fetch_field gets table alias, not real table name
After a SQL 'select .... from tablename alias' the mysql_fetch_field function returns a value $result=>table which will contain the alias, not the actual table name. Is there a way to get the actual table name ? I am running mysql 4.1 and php 4.4 ...

Web resources about - Get the primary key name of a table - comp.databases.oracle.misc

Primary market - Wikipedia, the free encyclopedia
of securities dealers. The process of selling new issues to investors is called underwriting . In the case of a new stock issue , this sale is ...

Father John Walshe stops saying mass at St John Vianney's Primary School after parent protest
A Catholic priest will no longer conduct&nbsp;mass at a Melbourne primary school after parents withdrew their&nbsp;children in protest over sexual ...

Father John Walshe stops saying mass at St John Vianney's Primary School after parent protest
A Catholic priest will no longer conduct mass at a Melbourne primary school after parents withdrew their children in protest over sexual abuse ...

US presidential election 2016: primary results and calendar
... nationwide process to choose their nominees for the next presidential election — to be held in November. Each party holds a caucus or a primary ...

Cable Coverage Plans for South Carolina Primary, Nevada Caucus
... Sellers , Van Jones and S.E. Cupp delivering analysis. 6 p.m. Blitzer, Tapper and Bash are back for coverage of South Carolina GOP primary, ...

A Primary Proposal
I hate the primaries as they currently exist, so here is my proposal for a new way to do them. In 2020, take the census, and create five groups ...

New York Pair Asks Court to Throw Cruz Off Primary Ballot
Republican presidential hopeful Ted Cruz should be struck from the ballot because he isn’t a “natural-born citizen,” two New Yorkers claim in ...

What to watch for ahead of the S.C. primary - Videos - CBS News
... political director John Dickerson joins “CBS This Morning” from Washington to discuss the latest CBS News poll and the South Carolina primary ...

How to win the South Carolina GOP primary
CNN International How to win the South Carolina GOP primary CNN International Gibbs Knotts is professor and department chair of political ...

Here’s Why the South Carolina Primary Is So Important
The 2016 presidential election will sweep through South Carolina this weekend. Republicans there will cast primary votes, with the Democrats ...

Resources last updated: 2/20/2016 6:07:24 PM