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

From: Cale, Rick T (Richard) <RICHARD.T.CALE_at_saic.com>
Date: Wed, 28 Nov 2001 09:46:43 -0800
Message-ID: <F001.003CF3FD.20011128092835@fatcity.com>

I have
had the same type of requirement and decided to use deferrable constraints.

Works
great!

Rick

-----Original Message-----From: Igor Neyman [mailto:ineyman_at_perceptron.com]Sent: Wednesday, November 28, 2001 11:56 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Script to Disable Constraint, Change Value, then Enable Const
 Not good approach.
 Instead, use 'deferrable constraints, should work in your situation.

 Igor Neyman, OCP DBA<A
 href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com

Original Message -----
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From:
<A title=dwagoner_at_arsenaldigital.com
href="mailto:dwagoner_at_arsenaldigital.com">David Wagoner To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

    Sent: Wednesday, November 28, 2001
    11:24 AM
    Subject: RE: Script to Disable
    Constraint, Change Value, then Enable Const

<FONT

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

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

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

 face="Courier New" color=blue size=2> 
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 1
3.
It is necessary to disable the FK constraint in order to update both tables
4.
Enable the FK constraint successfully
<FONT

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

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

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

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

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


<!---size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'> AUTOTEXTLIST \s "E-mail Signature" <FONT

face=Arial color=blue size=2>David B. Wagoner
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-STYLE: italic; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Database Administrator
<SPAN

 style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Arsenal Digital Solutions Worldwide Inc.
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">4815 Emperor Blvd., Suite 110
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Durham, NC 27703
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Tel. (919) 941-4645
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Fax (919) 474-0735
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Email
<FONT

 face="Times New Roman">mailto:dwagoner_at_arsenaldigital.com
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Web
<SPAN

 style="FONT-FAMILY: 'Times New Roman'">http://www.arsenaldigital.com/
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
<SPAN

 style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN

    style="mso-spacerun: yes">

<SPAN

    style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">***<SPAN 
    style="mso-spacerun: yes">  NOTICE<SPAN 
    style="mso-spacerun: yes">  ***<FONT face=Arial 
    color=blue size=1><SPAN 
    style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Arial; mso-color-alt: windowtext; mso-bidi-font-size: 12.0pt">

<SPAN

style="FONT-SIZE: 8pt; COLOR: blue; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">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'><SPAN

class=EmailStyle20>
-----Original Message-----From: Mark
 Leith [mailto:mark_at_cool-tools.co.uk]Sent: Wednesday, November 28, 2001 5:20 AMTo: Multiple recipients
 of list ORACLE-LSubject:
 RE: Script to Disable Constraint, Change Value, then Enable 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?
<FONT face="Times New Roman"

    color=black size=3><SPAN 
    style="FONT-SIZE: 12pt; COLOR: black"> <FONT 
    color=black><SPAN 
    style="COLOR: black; mso-color-alt: windowtext">

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.. 
<FONT face="Times New Roman"

    color=black size=3><SPAN 
    style="FONT-SIZE: 12pt; COLOR: black"> <FONT 
    color=black><SPAN 
    style="COLOR: black; mso-color-alt: windowtext">

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 WagonerSent: 27 November 2001 21:30To: Multiple
 recipients of list ORACLE-LSubject: Script to Disable Constraint, Change Value, then Enable Constrain
<FONT

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

 face=Arial color=black size=2>
<FONT

 face=Arial color=black size=2>Does anyone have a script that will do the following:
<FONT

 face=Arial color=black size=2>
<P class=MsoNormal

    style="MARGIN-LEFT: 1.5in; TEXT-INDENT: -0.25in; mso-list: l1 level1 lfo1; tab-stops: list 1.0in"><SPAN 
    class=EmailStyle19><SPAN 
    style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">1.<FONT

face="Times New Roman" size=1>
<FONT

 face=Arial color=black size=2>Accept user input for old data value 
<P class=MsoNormal

    style="MARGIN-LEFT: 1.5in; TEXT-INDENT: -0.25in; mso-list: l1 level1 lfo1; tab-stops: list 1.0in"><SPAN 
    class=EmailStyle19><SPAN 
    style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">2.<FONT

face="Times New Roman" size=1>
<FONT

 face=Arial color=black size=2>Accept user input for new data value 
<P class=MsoNormal

    style="MARGIN-LEFT: 1.5in; TEXT-INDENT: -0.25in; mso-list: l1 level1 lfo1; tab-stops: list 1.0in"><SPAN 
    class=EmailStyle19><SPAN 
    style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">3.<FONT

    style="MARGIN-LEFT: 1.5in; TEXT-INDENT: -0.25in; mso-list: l1 level1 lfo1; tab-stops: list 1.0in"><SPAN 
    class=EmailStyle19><SPAN 
    style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">4.<FONT

face="Times New Roman" size=1>
<FONT

 face=Arial color=black size=2>Update record with new data value 
<P class=MsoNormal

    style="MARGIN-LEFT: 1.5in; TEXT-INDENT: -0.25in; mso-list: l1 level1 lfo1; tab-stops: list 1.0in"><SPAN 
    class=EmailStyle19><SPAN 
    style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">5.<FONT

face="Times New Roman" size=1>
<FONT

 face=Arial color=black size=2>Enable constraint

<FONT

 face=Arial color=black size=2>
<FONT

 face=Arial color=black size=2>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.
<FONT

 face=Arial color=black size=2>
<FONT

 face=Arial color=black size=2>
<FONT

 face=Arial color=black size=2>TIA,
<FONT

 face=Arial color=black size=2>
<FONT

 face=Arial color=black size=2>david
<FONT

 face=Arial color=black size=2>
<span

style="mso-spacerun: yes"> AUTOTEXTLIST \s "E-mail Signature" <FONT

face=Arial color=blue size=2>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
<FONT

 face="Times New Roman">mailto:dwagoner_at_arsenaldigital.com
Web
<SPAN

 style="FONT-FAMILY: 'Times New Roman'">http://www.arsenaldigital.com/
 
<SPAN

    style="mso-spacerun: yes">

<SPAN

    style="mso-spacerun: yes">                    <FONT

face=Arial color=black size=1><SPAN

    style="FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">***<SPAN 
    style="mso-spacerun: yes">  NOTICE<SPAN 
    style="mso-spacerun: yes">  ***<FONT face=Arial 
    color=black size=1><SPAN 
    style="FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Arial; mso-color-alt: windowtext; mso-bidi-font-size: 12.0pt">

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

    color=black> <FONT 
    color=black><SPAN 
    style="COLOR: black; mso-color-alt: windowtext">

Received on Wed Nov 28 2001 - 11:46:43 CST