RE: Script to Disable Constraint, Change Value, then Enable Const
Well said Mark. David, you could also re-able the constraint 'novalidate' if you wanted to ignore the validity of any new input. If that was the case then why have an integrity constraint in the first place? John -Original Message-From: Mark Leith [mailto:[EMAIL PROTECTED]]Sent: 28 November 2001 10:20To: 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? 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-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 Listers, Does anyone have a script that will do the following: Accept user input for old data value Accept user input for new data value Disable table constraint Update record with new data value 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 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:[EMAIL PROTECTED] 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. >> This electronic message contains information from the mmO2 plc Group which may be privileged or confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or email (to the numbers or address above) immediately. >>
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: 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 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:[EMAIL PROTECTED] 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- From: Mark Leith [mailto:[EMAIL PROTECTED]] 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.. I struggle to see why you would want to do this - do you have any more info? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 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 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:[EMAIL PROTECTED] 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.
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[EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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-From: Mark Leith [mailto:[EMAIL PROTECTED]]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? 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-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 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 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:[EMAIL PROTECTED] 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.
RE: Script to Disable Constraint, Change Value, then Enable Const
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 dwagonerTo: Multiple recipients of list ORACLE-L @arsenaldigit[EMAIL PROTECTED] al.com cc: Sent by: rootSubject: 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:[EMAIL PROTECTED] 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
Re: Script to Disable Constraint, Change Value, then Enable Const
As far as I remember, there is such a script at http://www.oriole.com, in the X-rated section - as the name implies, not full-heartedly recommended. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Script to Disable Constraint, Change Value, then Enable Const
I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]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[EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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-From: Mark Leith [mailto:[EMAIL PROTECTED]]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? 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-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 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 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:[EMAIL PROTECTED] 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.
RE: Script to Disable Constraint, Change Value, then Enable Const
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. SQL set constraints all deferred 2 update table1 set host_name = 'tigerz' where host_name = 'tiger' 3* update table2 set host_name = 'tigerz' where host_name = 'tiger'; SQL update host set host_name = 'tigerz' where host_name = 'tiger' * ERROR at line 2: ORA-00933: SQL command not properly ended TIA, 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:[EMAIL PROTECTED] 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- From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]] 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 I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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- From: Mark Leith [mailto:[EMAIL PROTECTED]] 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.. I struggle to see why you would want to do this - do you have any more info? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 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 David B. Wagoner Database
RE: Script to Disable Constraint, Change Value, then Enable Const
David, You need a semi-colon after line2 i.e., update table1 ...; Rick -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 1:16 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const 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. SQL set constraints all deferred 2 update table1 set host_name = 'tigerz' where host_name = 'tiger' 3* update table2 set host_name = 'tigerz' where host_name = 'tiger'; SQL update host set host_name = 'tigerz' where host_name = 'tiger' * ERROR at line 2: ORA-00933: SQL command not properly ended TIA, 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:[EMAIL PROTECTED] 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-From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 12:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]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[EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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-From: Mark Leith [mailto:[EMAIL PROTECTED]]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? 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-From:
RE: Script to Disable Constraint, Change Value, then Enable Const
I know it looks like a semi-colon error, but its not. Please send me one of your examples that you know works and Ill modify and try here. 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:[EMAIL PROTECTED] 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- From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 28, 2001 2:35 PM To: Multiple recipients of list ORACLE-L Subject: RE: Script to Disable Constraint, Change Value, then Enable Const David, You need a semi-colon after line2 i.e., update table1 ...; Rick -Original Message- From: David Wagoner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 28, 2001 1:16 PM To: Multiple recipients of list ORACLE-L Subject: RE: Script to Disable Constraint, Change Value, then Enable Const 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. SQL set constraints all deferred 2 update table1 set host_name = 'tigerz' where host_name = 'tiger' 3* update table2 set host_name = 'tigerz' where host_name = 'tiger'; SQL update host set host_name = 'tigerz' where host_name = 'tiger' * ERROR at line 2: ORA-00933: SQL command not properly ended TIA, 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:[EMAIL PROTECTED] 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- From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]] 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 I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message- From: Igor Neyman [mailto:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 28, 2001 5:20 AM To: Multiple recipients of list ORACLE-L Subject: RE: Script
RE: Script to Disable Constraint, Change Value, then Enable Const
You have to create the constraints to support the deferred mode. Drop them and recreate them DEFERRABLE. Also you can't do edit in SQLPLUS and put these three lines in one window. Either put them in notepad and then cut and paste in sqlplus directly or create sql script that look like: set constraints all deferred; update table1 set host_name = 'tigerz' where host_name = 'tiger'; update table2 set host_name = 'tigerz' where host_name = 'tiger'; Regards, Waleed -Original Message-From: David Wagoner [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 1:15 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const 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. SQL set constraints all deferred 2 update table1 set host_name = 'tigerz' where host_name = 'tiger' 3* update table2 set host_name = 'tigerz' where host_name = 'tiger'; SQL update host set host_name = 'tigerz' where host_name = 'tiger' * ERROR at line 2: ORA-00933: SQL command not properly ended TIA, 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:[EMAIL PROTECTED] 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-From: Cale, Rick T (Richard) [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 28, 2001 12:29 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Script to Disable Constraint, Change Value, then Enable Const I have had the same type of requirement and decided to use deferrable constraints. Works great! Rick -Original Message-From: Igor Neyman [mailto:[EMAIL PROTECTED]]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[EMAIL PROTECTED] - Original Message - From: David Wagoner To: 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: 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 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:[EMAIL PROTECTED] 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-From: Mark Leith [mailto:[EMAIL PROTECTED]]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
