RE: Script to Disable Constraint, Change Value, then Enable Const

From: <tday6_at_csc.com>
Date: Wed, 28 Nov 2001 09:41:36 -0800
Message-ID: <F001.003CF424.20011128093307@fatcity.com>

OK. Here's what I do in that situation:

Here's the main driver

disable foreign keys on <parent_table.column> alter table CHILD1 disable constraint FK1; alter table CHILD2 disable constraint FK2; --run the detail_update.sql for each pair of values, ie, the current value and the
value that it will be set to
@ex_detail_update
re-enable foreign keys on <parent_table.column> alter table CHILD1 enable constraint FK1; alter table CHILD2 enable constraint FK2;

Here's the ex_detail_update.sql - basically it will replace parameter 1 with parameter 2

@detail_update 1012 1013
@detail_update 1011 1012
@detail_update 1010 1011
@detail_update 1009 1010

Here's where the nitty gritty happens -- detail_update.sql

update table CHILD1 set FK_ID = &&2 where FK_ID = &&1;
update table CHILD2 set FK_ID = &&2 where FK_ID = &&1;
update table PARENT set PK_ID = &&2 where PK_ID = &&1;

COMMIT
/

When it's done it falls out to the next set of pairs. When you run out of pairs then the top level script will re-enable your constraints.

                    David Wagoner                                                      
    
                    <dwagoner            To:     Multiple recipients of list ORACLE-L  
    
                    @arsenaldigit        <ORACLE-L_at_fatcity.com>                        
    
                    al.com>              cc:                                           
    
                    Sent by: root        Subject:     RE: Script to Disable 
Constraint,    
                                         Change Value, then Enable Const               
    
                                                                                       
    
                    11/28/2001                                                         
    
                    11:24 AM                                                           
    
                    Please                                                             
    
                    respond to                                                         
    
                    ORACLE-L

I can see the confusion here. The point is not to let someone enter data

that would violate the referential integrity. Let me explain with an

example:

1. User wants to update a primary key record in parent table

2. Dependent data exists in a child table so the user gets an error while trying to perform step

3. It is necessary to disable the FK constraint in order to update both tables

4. Enable the FK constraint successfully

Does that make sense? This is a process we have to do routinely and it has

happened in the past that the FK was mistakenly not re-enabled, which

allowed "illegal" data to be loaded later. Thus the need for a script.

David B. Wagoner

Database Administrator

Arsenal Digital Solutions Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email mailto:dwagoner_at_arsenaldigital.com

Web http://www.arsenaldigital.com/

*** NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you.

-----Original Message-----
Sent: Wednesday, November 28, 2001 5:20 AM To: Multiple recipients of list ORACLE-L Constrain

How could this be user proof? You are essentially disabling the constraint that WILL enforce data integrity, then letting the user input whatever rubbish he wants to, and are then going to try and enable the constraint afterwards?

Not a good approach.. How can you ensure that the user hasn't put a duplicate value in (unique constraint) or something else that might break the constraint rule? The only way you are going to know is when you try and re-enable the constraint it will fail..

I struggle to see why you would want to do this - do you have any more info?

-----Original Message-----
ner
Sent: 27 November 2001 21:30
To: Multiple recipients of list ORACLE-L ain

Listers,

Does anyone have a script that will do the following:

1. Accept user input for old data value

2. Accept user input for new data value

3. Disable table constraint

4. Update record with new data value

5. Enable constraint

A script like this would help ensure that constraints are not left "off" after updates, allowing "illegal" data into the tables. Good user-proof script I would think.

TIA, david