Inserting data and temporarily ignoring constraint
Larry asked whether INSERT has syntax to temporarily bypass table constraints. Art Kagel answered: wrap the work in a transaction and use "SET CONSTRAINTS ALL DEFERRED" (or name specific constraints) between BEGIN WORK and COMMIT WORK. He clarified the deferral applies only to that one transaction, not globally, so constraints are enforced again afterwards. Marcus Haarmann added that the COMMIT will fail with an error if the data still violates constraints at that point, and Paul Watson noted an exception if violations tables are in use. Question resolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Isn't there a syntax that you can add to an INSERT command to ignore constraints during the insert? I need to do this for several tables. Larry
begin work; set constraints all deferred; -- Or specific constraints on specific tables. insert ... ... commit work; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > Isn't there a syntax that you can add to an INSERT command to ignore > constraints during the insert? I need to do this for several tables. > > Larry > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1143f0a6d88c420539d21b9e
Thank you. After the transaction do the constraints automatically go back to enforced, or do you have to run another SET CONSTRAINT command? ________________________________ From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel <art.kagel@gmail.com> Sent: Thursday, August 11, 2016 3:04 PM To: ids@iiug.org Subject: Re: Inserting data and temporarily ignoring co.... [37586] begin work; set constraints all deferred; -- Or specific constraints on specific tables. insert ... .... commit work; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com<http://www.askdbmgt.com>; ASK Database Management - Home<http://www.askdbmgt.com/>; www.askdbmgt.com This is the site for Art S. Kagel's consultancy. The soaring majesty and beauty in the image above hides the complex ecology and detail of its existence. Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote: > Isn't there a syntax that you can add to an INSERT command to ignore > constraints during the insert? I need to do this for several tables. > > Larry > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1143f0a6d88c420539d21b9e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
The deferred command delays constraint checking until the commit and is only in effect for that one tranaction. Not globally. Art On Aug 11, 2016 17:36, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote: > Thank you. > > After the transaction do the constraints automatically go back to > enforced, or > do you have to run another SET CONSTRAINT command? > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel > <art.kagel@gmail.com> > Sent: Thursday, August 11, 2016 3:04 PM > To: ids@iiug.org > Subject: Re: Inserting data and temporarily ignoring co.... [37586] > > begin work; > set constraints all deferred; -- Or specific constraints on specific > tables. > insert ... > ..... > commit work; > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com<http://www.askdbmgt.com>; > > ASK Database Management - Home<http://www.askdbmgt.com/>; > www.askdbmgt.com > This is the site for Art S. Kagel's consultancy. The soaring majesty and > beauty in the image above hides the complex ecology and detail of its > existence. > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> > wrote: > > > Isn't there a syntax that you can add to an INSERT command to ignore > > constraints during the insert? I need to do this for several tables. > > > > Larry > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1143f0a6d88c420539d21b9e > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144c0d6bc0b0c0539d33cb0
You will get an error when commit is executed in case the constraints are not met at that stage. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "Art Kagel" <art.kagel@gmail.com> An: ids@iiug.org Gesendet: Freitag, 12. August 2016 00:25:14 Betreff: Re: Inserting data and temporarily ignoring co.... [37588] The deferred command delays constraint checking until the commit and is only in effect for that one tranaction. Not globally. Art On Aug 11, 2016 17:36, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote: > Thank you. > > After the transaction do the constraints automatically go back to > enforced, or > do you have to run another SET CONSTRAINT command? > > ________________________________ > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel > <art.kagel@gmail.com> > Sent: Thursday, August 11, 2016 3:04 PM > To: ids@iiug.org > Subject: Re: Inserting data and temporarily ignoring co.... [37586] > > begin work; > set constraints all deferred; -- Or specific constraints on specific > tables. > insert ... > ..... > commit work; > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com<http://www.askdbmgt.com>; > > ASK Database Management - Home<http://www.askdbmgt.com/>; > www.askdbmgt.com > This is the site for Art S. Kagel's consultancy. The soaring majesty and > beauty in the image above hides the complex ecology and detail of its > existence. > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> > wrote: > > > Isn't there a syntax that you can add to an INSERT command to ignore > > constraints during the insert? I need to do this for several tables. > > > > Larry > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1143f0a6d88c420539d21b9e > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144c0d6bc0b0c0539d33cb0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Unless you are using violations tables ... Paul Watson Oninit www.oninit.com +1 913 387 7529 On unit® is a Registered Trademark of Oninit LLC On Aug 12, 2016, at 06:15, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > You will get an error when commit is executed in case the constraints are not > met at that stage. > > Marcus Haarmann > > ----- Ursprüngliche Mail ----- > > Von: "Art Kagel" <art.kagel@gmail.com> > An: ids@iiug.org > Gesendet: Freitag, 12. August 2016 00:25:14 > Betreff: Re: Inserting data and temporarily ignoring co.... [37588] > > The deferred command delays constraint checking until the commit and is > only in effect for that one tranaction. Not globally. > > Art > >> On Aug 11, 2016 17:36, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote: >> >> Thank you. >> >> After the transaction do the constraints automatically go back to >> enforced, or >> do you have to run another SET CONSTRAINT command? >> >> ________________________________ >> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel >> <art.kagel@gmail.com> >> Sent: Thursday, August 11, 2016 3:04 PM >> To: ids@iiug.org >> Subject: Re: Inserting data and temporarily ignoring co.... [37586] >> >> begin work; >> set constraints all deferred; -- Or specific constraints on specific >> tables. >> insert ... >> ..... >> commit work; >> >> Art >> >> Art S. Kagel, President and Principal Consultant >> ASK Database Management >> www.askdbmgt.com<http://www.askdbmgt.com>; >> >> ASK Database Management - Home<http://www.askdbmgt.com/>; >> www.askdbmgt.com >> This is the site for Art S. Kagel's consultancy. The soaring majesty and >> beauty in the image above hides the complex ecology and detail of its >> existence. >> >> Blog: http://informix-myview.blogspot.com/ >> >> Disclaimer: Please keep in mind that my own opinions are my own opinions >> and do not reflect on the IIUG, nor any other organization with which I am >> associated either explicitly, implicitly, or by inference. Neither do >> those opinions reflect those of other individuals affiliated with any >> entity with which I am affiliated nor those of the entities themselves. >> >> On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> >> wrote: >> >>> Isn't there a syntax that you can add to an INSERT command to ignore >>> constraints during the insert? I need to do this for several tables. >>> >>> Larry >>> >>> >>> ************************************************************ >>> ******************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> --001a1143f0a6d88c420539d21b9e >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1144c0d6bc0b0c0539d33cb0 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Marcus, forgot to mention that little detail. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 12, 2016 at 7:15 AM, Marcus Haarmann <marcus.haarmann@midoco.de> wrote: > You will get an error when commit is executed in case the constraints are > not > met at that stage. > > Marcus Haarmann > > ----- Ursprüngliche Mail ----- > > Von: "Art Kagel" <art.kagel@gmail.com> > An: ids@iiug.org > Gesendet: Freitag, 12. August 2016 00:25:14 > Betreff: Re: Inserting data and temporarily ignoring co.... [37588] > > The deferred command delays constraint checking until the commit and is > only in effect for that one tranaction. Not globally. > > Art > > On Aug 11, 2016 17:36, "LARRY SORENSEN" <LSORENSEN25@msn.com> wrote: > > > Thank you. > > > > After the transaction do the constraints automatically go back to > > enforced, or > > do you have to run another SET CONSTRAINT command? > > > > ________________________________ > > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of Art Kagel > > <art.kagel@gmail.com> > > Sent: Thursday, August 11, 2016 3:04 PM > > To: ids@iiug.org > > Subject: Re: Inserting data and temporarily ignoring co.... [37586] > > > > begin work; > > set constraints all deferred; -- Or specific constraints on specific > > tables. > > insert ... > > ..... > > commit work; > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com<http://www.askdbmgt.com>; > > > > ASK Database Management - Home<http://www.askdbmgt.com/>; > > www.askdbmgt.com > > This is the site for Art S. Kagel's consultancy. The soaring majesty and > > beauty in the image above hides the complex ecology and detail of its > > existence. > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Thu, Aug 11, 2016 at 4:54 PM, LARRY SORENSEN <LSORENSEN25@msn.com> > > wrote: > > > > > Isn't there a syntax that you can add to an INSERT command to ignore > > > constraints during the insert? I need to do this for several tables. > > > > > > Larry > > > > > > > > > ************************************************************ > > > ******************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1143f0a6d88c420539d21b9e > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ************************************************************ > > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1144c0d6bc0b0c0539d33cb0 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1140b160aac1b20539dfd071