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

From: David Wagoner <dwagoner_at_arsenaldigital.com>
Date: Wed, 28 Nov 2001 10:43:07 -0800
Message-ID: <F001.003CF5BE.20011128101529@fatcity.com>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Rick, thanks for your input. When I test
the deferred constraint in SQL*Plus I get an error. What am I doing wrong?
I did not find much information in the docs about deferred constraints.

<span

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

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> SQL> set constraints all deferred

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 2 update table1 set host_name = 'tigerz' where host_name = 'tiger'

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 3* update table2 set host_name = 'tigerz' where host_name = 'tiger';

<span

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

<span

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

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>update host set host_name = 'tigerz' where host_name = 'tiger'

<span

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

<span

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

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>ORA-00933: SQL command not properly ended

<span

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

<span

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

<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: Cale, Rick T (Richard)
[mailto:RICHARD.T.CALE_at_saic.com]
Sent: Wednesday, November 28, 2001
12:29 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: Script to Disable
Constraint, Change Value, then Enable Const

<span

style='font-size:12.0pt'>

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>I have had the same type of requirement and decided to use deferrable constraints.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Works great!

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Rick

-----Original Message----- From: Igor Neyman
[mailto:ineyman_at_perceptron.com]
Sent: Wednesday, November 28, 2001
11:56 AM
To: Multiple recipients of list
ORACLE-L
Subject: 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 ineyman_at_perceptron.com

<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;
margin-left:75.75pt;background:#E4E4E4;border:none;mso-border-left-alt:solid black 1.5pt; padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'>

From: <a
href="mailto:dwagoner_at_arsenaldigital.com" title="dwagoner_at_arsenaldigital.com">David Wagoner

To: <a href="mailto:ORACLE-L_at_fatcity.com" title="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

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:

<p class=MsoNormal style='margin-left:117.75pt;text-indent:-24.0pt;tab-stops:
list 42.0pt;mso-layout-grid-align:none;border:none;mso-border-left-alt:solid black 1.5pt; padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'>1. User wants to update a primary key record in parent table

<p class=MsoNormal style='margin-left:117.75pt;text-indent:-24.0pt;tab-stops:
list 42.0pt;mso-layout-grid-align:none;border:none;mso-border-left-alt:solid black 1.5pt; padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'>2. Dependent data exists in a child table so the user gets an error while trying to perform step 1

<p class=MsoNormal style='margin-left:117.75pt;text-indent:-24.0pt;tab-stops:
list 42.0pt;mso-layout-grid-align:none;border:none;mso-border-left-alt:solid black 1.5pt; padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'>3. It is necessary to disable the FK constraint in order to update both tables

<p class=MsoNormal style='margin-left:117.75pt;text-indent:-24.0pt;tab-stops:
list 42.0pt;mso-layout-grid-align:none;border:none;mso-border-left-alt:solid black 1.5pt; padding:0in;mso-padding-alt:0in 0in 0in 4.0pt'>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.

<!---size: 10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> AUTOTEXTLIST
\s "E-mail Signature" 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/

yes">

<span style="mso-spacerun:

yes">                                                                <font

size=1 color=blue face=Arial>*** 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

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

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

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.

 AUTOTEXTLIST \s "E-mail Signature" David B. Wagoner

yes">

<span

style='font-size:8.0pt;mso-bidi-font-size:12.0pt;font-family:Arial;color:black'>*** 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.

 Received on Wed Nov 28 2001 - 12:43:07 CST