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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.