Primary Key Constraint Analysis (dconpk.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive primary key constraint analysis by:

• Listing all primary key constraints for specified schemas and tables
• Showing the column composition and order within each primary key
• Displaying constraint names and properties
• Organizing output by owner, table, and column position
• Supporting wildcard patterns for flexible filtering

Script

rem  dconfkc.sql
rem
set linesize 132
rem
ttitle 'Primary Key Table Constraint Columns'
rem
col owner format a12 heading 'OWNER'
col table_name format a18 heading 'TABLE NAME'
col constraint_name format a30 heading 'CONSTRAINT NAME'
col constraint_type format a1 heading 'T'
col status format a8 heading 'STATUS'
col target_table format a18 heading 'TARGET TABLE NAME'
col position format 99 heading 'POS'
col column_name format a30 heading 'COLUMN NAME'
col delete_rule format a9 heading 'DELETE|RULE'
rem
break on owner on table_name on constraint_name on target_table on delete_rule
rem
select c.owner, c.table_name, c.constraint_name,
       cc.table_name target_table, c.delete_rule,
       col.position,
       col.column_name
  from sys.dba_cons_columns col,
       sys.dba_constraints cc,
       sys.dba_constraints c
 where c.owner like upper('&owner')
   and c.table_name like upper('&table')
   and c.owner = cc.owner
   and c.constraint_name = cc.constraint_name
   and c.constraint_type = 'P'
   and c.owner = col.owner
   and c.table_name = col.table_name
   and c.constraint_name = col.constraint_name
 order by c.owner, c.table_name, c.constraint_name, col.position;
rem
set linesize 80

Usage

SQL> @dconpk.sql
Enter value for owner: HR
Enter value for table: %

Parameters

• owner: Schema owner (use % for all schemas, specific name for single schema)
• table: Table name pattern (use % for all tables, specific name for single table)

Required Privileges

• SELECT on SYS.DBA_CONS_COLUMNS
• SELECT on SYS.DBA_CONSTRAINTS

Sample Output

Primary Key Table Constraint Columns

OWNER        TABLE NAME         CONSTRAINT NAME                TARGET TABLE NAME  DELETE    POS COLUMN NAME
                                                                                 RULE
------------ ------------------ ------------------------------ ------------------ --------- --- ------------------------------
HR           DEPARTMENTS        DEPT_ID_PK                                                   1 DEPARTMENT_ID

HR           EMPLOYEES          EMP_EMAIL_UK                                                 1 EMAIL
                                EMP_EMP_ID_PK                                                1 EMPLOYEE_ID

HR           JOBS               JOB_ID_PK                                                    1 JOB_ID

HR           LOCATIONS          LOC_ID_PK                                                    1 LOCATION_ID

SALES        ORDER_ITEMS        ORDER_ITEMS_PK                                               1 ORDER_ID
                                                                                             2 PRODUCT_ID

SALES        ORDERS             ORDERS_PK                                                    1 ORDER_ID

Key Output Columns

• OWNER: Schema that owns the table
• TABLE NAME: Name of the table with the primary key
• CONSTRAINT NAME: Name of the primary key constraint
• TARGET TABLE NAME: Referenced table (empty for primary keys)
• DELETE RULE: Deletion rule (empty for primary keys)
• POS: Position of column within the primary key (1, 2, 3…)
• COLUMN NAME: Name of the column that’s part of the primary key

Understanding Primary Key Structure

Single Column Primary Keys

EMPLOYEES    EMP_EMP_ID_PK    1    EMPLOYEE_ID

• Simple primary key on one column
• Most common and efficient design
• Direct unique identification

Composite Primary Keys

ORDER_ITEMS  ORDER_ITEMS_PK   1    ORDER_ID
                              2    PRODUCT_ID

• Primary key spans multiple columns
• All columns together must be unique
• Common in junction/intersection tables

Primary Key Analysis

Design Quality Assessment

• Single Column: Generally preferred for performance
• Composite Keys: Necessary for many-to-many relationships
• Column Order: Important for index efficiency
• Data Types: Numeric keys typically perform better

Performance Considerations

• Index Support: Primary key automatically creates unique index
• Foreign Key References: Performance impact on referencing tables
• Join Performance: Primary key order affects join efficiency

Common Use Cases

1. Schema Documentation

   • Document primary key structure
   • Understand table relationships
   • Validate design patterns

2. Database Migration

   • Extract primary key definitions
   • Verify constraint recreation
   • Compare source and target schemas

3. Performance Analysis

   • Analyze primary key efficiency
   • Review composite key column order
   • Identify optimization opportunities

Advanced Analysis Queries

Find tables without primary keys:

SELECT owner, table_name
FROM dba_tables
WHERE (owner, table_name) NOT IN (
  SELECT owner, table_name
  FROM dba_constraints
  WHERE constraint_type = 'P'
)
AND owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP')
ORDER BY owner, table_name;

Analyze composite primary key complexity:

SELECT owner, table_name, constraint_name, COUNT(*) column_count
FROM dba_cons_columns cc
WHERE (owner, table_name, constraint_name) IN (
  SELECT owner, table_name, constraint_name
  FROM dba_constraints
  WHERE constraint_type = 'P'
)
GROUP BY owner, table_name, constraint_name
HAVING COUNT(*) > 1
ORDER BY column_count DESC;

Check primary key data types:

SELECT cc.owner, cc.table_name, cc.column_name, tc.data_type, tc.data_length
FROM dba_cons_columns cc, dba_tab_columns tc
WHERE cc.owner = tc.owner
AND cc.table_name = tc.table_name
AND cc.column_name = tc.column_name
AND (cc.owner, cc.table_name, cc.constraint_name) IN (
  SELECT owner, table_name, constraint_name
  FROM dba_constraints
  WHERE constraint_type = 'P'
)
ORDER BY cc.owner, cc.table_name, cc.position;

Primary Key Best Practices

Design Guidelines

1. Single Column When Possible

   • Use surrogate keys (sequences) for complex natural keys
   • Improve join performance
   • Simplify foreign key relationships

2. Stable Values

   • Primary key values should never change
   • Avoid business values that might change
   • Use immutable identifiers

3. Data Type Selection

   • NUMBER columns typically perform best
   • Avoid VARCHAR2 for primary keys when possible
   • Consider RAW for globally unique identifiers

Performance Optimization

-- Check primary key index usage
SELECT i.owner, i.table_name, i.index_name, i.uniqueness,
       s.num_rows, s.distinct_keys, s.clustering_factor
FROM dba_indexes i, dba_ind_statistics s
WHERE i.owner = s.owner
AND i.index_name = s.index_name
AND i.uniqueness = 'UNIQUE'
AND (i.owner, i.table_name) IN (
  SELECT owner, table_name FROM dba_constraints WHERE constraint_type = 'P'
);

Troubleshooting Primary Key Issues

Constraint Violations

• ORA-00001: Unique constraint violated
• Solution: Check for duplicate values before constraint creation

Performance Problems

• Slow Inserts: Large composite keys can slow DML
• Join Performance: Complex keys affect join operations
• Solution: Consider surrogate key design

Missing Primary Keys

-- Generate primary key creation scripts
SELECT 'ALTER TABLE ' || owner || '.' || table_name ||
       ' ADD CONSTRAINT pk_' || LOWER(table_name) ||
       ' PRIMARY KEY (/* specify columns */);' as ddl_statement
FROM dba_tables
WHERE (owner, table_name) NOT IN (
  SELECT owner, table_name FROM dba_constraints WHERE constraint_type = 'P'
)
AND owner = 'YOUR_SCHEMA';

Related Analysis

Foreign Key Dependencies

-- Find foreign keys referencing these primary keys
SELECT c.owner, c.table_name, c.constraint_name,
       r.owner ref_owner, r.table_name ref_table
FROM dba_constraints c, dba_constraints r
WHERE c.constraint_type = 'R'
AND c.r_constraint_name = r.constraint_name
AND r.constraint_type = 'P'
AND r.owner LIKE '&owner'
ORDER BY r.owner, r.table_name, c.owner, c.table_name;

Documentation Output

The script output can be used to:

• Generate data model documentation
• Create database design specifications
• Validate referential integrity design
• Plan database migration strategies

Related Scripts

• Foreign Key Analysis
• All Constraints
• Table Structure Analysis