Re: Modifying primary key field

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/07/23
Message-ID: <31f42925.5742967_at_dcsun4>

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.
>

You will have to drop the foreign keys that point from the child tables to the parent table. This sql*plus script (for 7.1 and above) will help in that respect. If you run it and give it the name of the parent table, it will spit out all of the alters for the child tables to re-establish the forieng key relationship. This will tell you what constraints to drop as well as create the script to recreate those constraints.

column fkey format a80 word_wrapped
select

'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey

from
( select a.table_name child_tname, a.constraint_name child_cons_name,

		 b.r_constraint_name parent_cons_name,
	     max(decode(position, 1,     '"'||column_name||'"',NULL)) || 
	     max(decode(position, 2,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 3,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 4,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 5,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 6,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 7,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 8,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 9,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,10,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,11,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,12,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,13,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,14,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,15,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,16,', '||'"'||column_name||'"',NULL)) 
			child_columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type = 'R'
   group by a.table_name, a.constraint_name, b.r_constraint_name ) child, ( select a.constraint_name parent_cons_name, a.table_name parent_tname,

	     max(decode(position, 1,     '"'||column_name||'"',NULL)) || 
	     max(decode(position, 2,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 3,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 4,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 5,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 6,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 7,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 8,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position, 9,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,10,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,11,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,12,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,13,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,14,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,15,', '||'"'||column_name||'"',NULL)) || 
	     max(decode(position,16,', '||'"'||column_name||'"',NULL)) 
			parent_columns

    from user_cons_columns a, user_constraints b    where a.constraint_name = b.constraint_name      and b.constraint_type in ( 'P', 'U' )    group by a.table_name, a.constraint_name ) parent where child.parent_cons_name = parent.parent_cons_name   and parent.parent_tname = upper('&1')
/

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Jul 23 1996 - 00:00:00 CEST