Q
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Querying a system table to get primary key info about a column

Which system table has the information about the primary keys of user defined tables that can be queried to find out the column of the primary key on supplying table name?

There are two system views you want: user_constraints and user_cons_columns (also note that you could use all_constraints or dba_constraints, etc).

For example, take a look at this script:

set lines 150
set pages 100
set feedback off

column cname format a35 heading 'Constraint'
column ctype format a11 heading 'Type'
column crule format a40 heading 'Constraint Rule'
column colname format a20 heading 'Column'
column cpos format 9 heading '#'
column crefer format a20 heading 'Refers To'
column csort noprint

break on ctype skip 2

select	a.constraint_type || a.constraint_name as csort,
	decode(a.constraint_type,'P','Primary Key',
	                         'R','Foreign Key',
	                         'C','Check',
	                         'Other') as ctype,
	a.table_name || '.' || a.constraint_name as cname,
	b.position as cpos,
	b.column_name as colname,
	a.search_condition as crule,
	a.r_constraint_name as crefer
  from user_constraints a, user_cons_columns b
 where a.constraint_name = b.constraint_name
 order by csort, b.position
/
This script will give you a list of all constraints (not just the primary key).

Here's another script you can run to get just the info for a specified table:

-- get_pkey.sql
set echo off
set serveroutput on
set verify off
set feedback off

clear screen

PROMPT What is the primary key for a table?
ACCEPT sp_table PROMPT 'Enter the table name: '

DECLARE
  v_table	user_constraints.table_name%TYPE := UPPER('&sp_table');
  v_name	user_cons_columns.column_name%TYPE;
BEGIN
  SELECT cc.column_name
    INTO v_name
    FROM user_constraints c, user_cons_columns cc
   WHERE c.table_name = v_table
     AND c.constraint_type = 'P'
     AND c.constraint_name = cc.constraint_name ;
  
  dbms_output.put_line('The primary key for the ' || lower(v_table) ||
  		       ' table is ' || lower(v_name));	       
END;
/
To run it:
SQL> @get_pkey
What is the primary key for a table?
Enter the table name: EMP
The primary key for the emp table is empno
SQL> 

For More Information


Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close