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

From: David Wagoner <dwagoner_at_arsenaldigital.com>
Date: Wed, 28 Nov 2001 08:55:02 -0800
Message-ID: <F001.003CF241.20011128082412@fatcity.com>

<font

size=2 color=blue face="Courier New">I can see the confusion here. The point is not to let someone enter data

<font

size=2 color=blue face="Courier New">that would violate the referential integrity. Let me explain with an

<font

size=2 color=blue face="Courier New">example:

<font

size=2 color=blue face="Courier New">

1.
<span

style='font-size:10.0pt;font-family:"Courier New";color:blue'>User wants to update a primary key record in parent table

2.
<span

style='font-size:10.0pt;font-family:"Courier New";color:blue'>Dependent data exists in a child table so the user gets an error while trying to perform step 1

3.
<span

style='font-size:10.0pt;font-family:"Courier New";color:blue'>It is necessary to disable the FK constraint in order to update both tables

4.
<span

style='font-size:10.0pt;font-family:"Courier New";color:blue'>Enable the FK constraint successfully

<font

size=2 color=blue face="Courier New">Does that make sense? This is a process we have to do routinely and it has

<font

size=2 color=blue face="Courier New">happened in the past that the FK was mistakenly not re-enabled, which

<font

size=2 color=blue face="Courier New">allowed "illegal" data to be loaded later. Thus the need for a script.

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>

<!---size:10.0pt;mso-bidi-font-size:

12.0pt;font-family:Arial'> AUTOTEXTLIST \s "E-mail Signature" David B. Wagoner

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial;color:blue; font-style:italic'>Database Administrator

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial;color:blue; font-weight:bold'>Arsenal Digital Solutions Worldwide Inc.

4815 Emperor Blvd., Suite 110

Durham, NC 27703

Tel. (919) 941-4645

Fax (919) 474-0735

Email <a href="mailto:dwagoner_at_arsenaldigital.com">mailto:dwagoner_at_arsenaldigital.com

Web <a href="http://www.arsenaldigital.com/">http://www.arsenaldigital.com/

<span style="mso-spacerun:

yes">

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

<!---size:10.0pt;mso-bidi-font-size:

12.0pt;font-family:Arial'>

-----Original
Message-----
From: Mark Leith
[mailto:mark_at_cool-tools.co.uk]
Sent: Wednesday, November 28, 2001
5:20 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Constrain

<span

style='font-size:12.0pt'>

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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?

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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..

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>I struggle to see why you would want to do this - do you have any more info?

-----Original Message----- From: root_at_fatcity.com
[mailto:root_at_fatcity.com]On Behalf Of David Wagoner
Sent: 27 November 2001 21:30
To: Multiple recipients of list
ORACLE-L
Subject: Script to Disable
Constraint, Change Value, then Enable Constrain

<font

size=2 color=black face=Arial>Listers,