Re: Modifying primary key field

From: Craig M. Wall <cwall_at_petersons.com>
Date: 1996/07/23
Message-ID: <MPLANET.31f4cb31cwall98968e_at_nntp.noc.netcom.net>


In article <31f42925.5742967_at_dcsun4>, tkyte_at_us.oracle.com says...
> On Tue, 23 Jul 1996 04:16:31 GMT, yimfam_at_pc.jaring.my (B. Yim) wrote:
>
> >Hello,
> >
> >I have a table with the primary key defined on a char(5) field. This
> >primary key is referenced by a lot of foreign keys in other tables.
> >I need to expand this field to char(6) but could not do it with the
> >alter table command - problem with integrity constraints violations.
> >Can anyone suggest a way to get around this problem.
> >
> >Thanks a lot.
> >

Appended to this post is an alternative script for handling primary and foreign key constraints/indexes/dependencies that differs from the solution in the previous post in that;

  1. It allows you to move the primary key's index to another tablespace
  2. It creates indexes for the foreign keys in another tablespace.

You can edit the script to tune the storage parameters, etc.

Craig M. Wall

/*
Filename = $dev_app_con:reanimator_pk.sql Purpose is to drop and re-create a primary key and handle all cascading

         constraint implications.
Invoked by the DBA when a developer has incorrectly created a

         primary key with an index in the wrong tablespace and
         it's too much trouble to re-create the table or pk manually.
         (Trouble being something like the institution_unit_contexts
         primary key being referenced by over thirty foreign keys that
         have to be dropped and re-created with DDL statements that
         aren't lying around on any disk or documented by developers.)
Note     - Just disabling the foreign keys that reference this pk
         will not allow the pk to be dropped. This script drops those
         fks and reanimates them after changes have been made to the pk.    
         This script is also good for re-directing a primary key's index
         to another tablespace for performance tuning.
         Assumes fks and pk are in the same schema and consists of single
         columns.

Syntax = $sqlplus -s / _at_~filename
Params = &user is username or schema, &target is table with problem pk,

         &tspace is the tablespace name where the pk index will reside. Output = auto_reanimator_pk.sql
Followup = Examine the output and make any changes before running as

           the schema owner (not DBA).

Author   = cmw
Revised  = 5/3/96
======================================================================== 
*/
prompt
accept user   prompt 'Enter the Username or Schema : '
accept target prompt 'Enter the Table Name of the Primary Key  : '
accept tspace prompt 'Enter the Tablespace Name for Index of PK : '
spool auto_reanimator_pk.sql
set heading off
set feedback off
set echo off
set serverout off
set verify off
set pagesize 0
--- Dropping Foreign Keys
select
'alter table &user..'||b.table_name||'
drop constraint '||b.constraint_name||';' from dba_constraints b,

      dba_constraints c
where c.table_name = upper('&target')

      and c.constraint_name = b.r_constraint_name
      and c.constraint_name in (select constraint_name from 
dba_constraints
                                where table_name = upper('&target')
                                and constraint_type = 'P'
                                and owner = upper('&user'))
      and c.owner = upper('&user')
      and b.owner = upper('&user')

order by b.table_name, b.constraint_name; -- Dropping Primary Key
select
'alter table &user..'||table_name||'
drop constraint '||constraint_name||';' from dba_constraints
where table_name = upper('&target')
and constraint_type = 'P'
and owner = upper('&user');
-- Creating Primary Key
select
'alter table  &user..'||b.table_name||' 
add constraint '||b.constraint_name||'   
primary key   ('||a.column_name||')

using index tablespace &tspace;'
from dba_cons_columns a,

     dba_constraints b
where b.table_name = upper('&target')

      and a.constraint_name = b.constraint_name
      and b.constraint_type = 'P'
      and b.owner = upper('&user')
      and a.owner = upper('&user');

---- Creating Foreign Keys
select
'alter table &user..'||b.table_name||' add constraint '||b.constraint_name||' foreign key ('||a.column_name||')
references &user..'||c.table_name||'('||d.column_name||');'
from dba_cons_columns a,
     dba_constraints  b,
     dba_constraints  c,
     dba_cons_columns d
where c.table_name = upper('&target')
      and a.constraint_name = b.constraint_name
      and d.constraint_name = c.constraint_name
      and b.r_constraint_name in (select constraint_name from 
dba_constraints
                                 where table_name = upper('&target')
                                 and constraint_type = 'P'
                                 and owner = upper('&user'))
      and c.constraint_name in (select constraint_name from 
dba_constraints
                                where table_name = upper('&target')
                                and constraint_type = 'P'
                                and owner = upper('&user'))
      and a.owner = upper('&user')
      and b.owner = upper('&user')
      and c.owner = upper('&user')
      and d.owner = upper('&user')

order by b.table_name, b.constraint_name; -- Dropping Indexes for Foreign Keys
select
'drop index &user..'||b.constraint_name||';'
from dba_cons_columns a,
     dba_constraints  b,
     dba_cons_columns d,
     dba_constraints  c
   where c.table_name = upper('&target')
      and a.constraint_name = b.constraint_name
      and d.constraint_name = c.constraint_name
      and b.r_constraint_name in (select constraint_name from 
dba_constraints
                                 where table_name = upper('&target')
                                 and constraint_type = 'P'
                                 and owner = upper('&user'))
      and c.constraint_name in (select constraint_name from 
dba_constraints
                                where table_name = upper('&target')
                                and constraint_type = 'P'
                                and owner = upper('&user'))
      and a.owner = upper('&user')
      and b.owner = upper('&user')
      and c.owner = upper('&user')
      and d.owner = upper('&user');

-- Creating Indexes for Foreign Keys
select
'create index &user..'||b.constraint_name||'
           on &user..'||b.table_name||'('||a.column_name||')
           tablespace &tspace; '
from dba_cons_columns a,
     dba_constraints  b,
     dba_cons_columns d,
     dba_constraints  c
   where c.table_name = upper('&target')
      and a.constraint_name = b.constraint_name
      and d.constraint_name = c.constraint_name
      and b.r_constraint_name in (select constraint_name from 
dba_constraints
                                 where table_name = upper('&target')
                                 and constraint_type = 'P'
                                 and owner = upper('&user'))
      and c.constraint_name in (select constraint_name from 
dba_constraints
                                where table_name = upper('&target')
                                and constraint_type = 'P'
                                and owner = upper('&user'))
      and a.owner = upper('&user')
      and b.owner = upper('&user')
      and c.owner = upper('&user')
      and d.owner = upper('&user')

order by b.table_name, b.constraint_name;

spool off
set verify on
set feedback on
-- start auto_reanimator_pk.sql
prompt
pause Press <RETURN> to exit
exit Received on Tue Jul 23 1996 - 00:00:00 CEST

Original text of this message