How to temporarily disable referential constraints on a table

Joerg Spilker

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

Hello,

can i disable referential constraints on a database (or better, on
some specific table)? I only can imagine the way of dropping all
constraints, doing my database work and then creating the constraints
from scratch.

Greetings, Joerg Spilker

Query Gurl

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

SET CONSTRAINTS ALL DEFERRED

~Tisha

"Joerg Spilker" <j...@jetsys.de> wrote in message
news:39771DF8...@jetsys.de...

Farmer, Byrd G - CFC

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01BFF279.602FE760
Content-Type: text/plain;
charset="iso-8859-1"

set constraints constraint_name disabled;

then when done

set constraints constraint_name enabled;

-----Original Message-----
From: Joerg Spilker [mailto:j...@jetsys.de]
Sent: Thursday, July 20, 2000 8:43 AM
To: inform...@iiug.org
Subject: How to temporarily disable referential constraints on a table

Hello,

can i disable referential constraints on a database (or better, on
some specific table)? I only can imagine the way of dropping all
constraints, doing my database work and then creating the constraints
from scratch.

Greetings, Joerg Spilker

------_=_NextPart_001_01BFF279.602FE760
Content-Type: text/html;
charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2651.75">
<TITLE>RE: How to temporarily disable referential constraints on a table</TITLE>
</HEAD>
<BODY>

set constraints constraint_name disabled;

then when done

set constraints constraint_name enabled;

-----Original Message-----
 From: Joerg Spilker [<A HREF="mailto:j...@jetsys.de">mailto:j...@jetsys.de</A>]
 Sent: Thursday, July 20, 2000 8:43 AM
 To: inform...@iiug.org
 Subject: How to temporarily disable referential constraints on a table

Hello,

can i disable referential constraints on a database (or better, on
 some specific table)? I only can imagine the way of dropping all
 constraints, doing my database work and then creating the constraints
 from scratch.

Greetings, Joerg Spilker

</BODY>
</HTML>
------_=_NextPart_001_01BFF279.602FE760--

Maria del Carmen Bustos

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

SET CONSTRAINTS FOR tab_name DISABLED

Art S. Kagel

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to j...@jetsys.de

If you REALLY need to disable them then take Byrd's version but if
what you REALLY need is to be able to load complex corellated data
without worrying about the order in which you update the tables, use
Query Gurl's suggestion and DEFER constraint checking until COMMIT
time.

A note to all: If you post "How do I do such and such a particular
thing" because that is the only way you could think of to solve your
problem you will rarely get the answer you REALLY NEED. Better to
post your problem and let use suggest the best solution(s). Think
how good it will feel when The Clown recommends doing just what you
had decided to do!

Art S. Kagel

Obnoxio The Clown

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

From: "Art S. Kagel" <ka...@bloomberg.net>

>
>A note to all: If you post "How do I do such and such a particular
>thing" because that is the only way you could think of to solve your
>problem you will rarely get the answer you REALLY NEED. Better to
>post your problem and let use suggest the best solution(s). Think
>how good it will feel when The Clown recommends doing just what you
>had decided to do!

Hey, look, nobody's perfect... :0)
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

jim lowry

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

>A note to all: If you post "How do I do such and such a particular
>thing" because that is the only way you could think of to solve your
>problem you will rarely get the answer you REALLY NEED. Better to
>post your problem and let use suggest the best solution(s). Think
>how good it will feel when The Clown recommends doing just what you
>had decided to do!

You mean people should THINK outside the box?

Mark D. Stock

unread,

Jul 20, 2000, 3:00:00 AM7/20/00

to

"Art S. Kagel" wrote:
>
> If you REALLY need to disable them then take Byrd's version but if
> what you REALLY need is to be able to load complex corellated data
> without worrying about the order in which you update the tables, use
> Query Gurl's suggestion and DEFER constraint checking until COMMIT
> time.
>

> A note to all: If you post "How do I do such and such a particular
> thing" because that is the only way you could think of to solve your
> problem you will rarely get the answer you REALLY NEED. Better to
> post your problem and let use suggest the best solution(s). Think
> how good it will feel when The Clown recommends doing just what you
> had decided to do!

Is that supposed to be a recommendation? :-O

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mds...@mydas.freeserve.co.uk |//////// /|
| http://www.informix.com http://www.informixhandbook.com |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |This email will self-destruct in |/// / ////|
| |10 sec. If you received this email |// / /////|
| |in error, sorry about the mess. |/ ////////|
+----------------------+-----------------------------------+-----------+

Art S. Kagel

unread,

Jul 21, 2000, 3:00:00 AM7/21/00

to jim lowry

jim lowry wrote:
>
> >A note to all: If you post "How do I do such and such a particular
> >thing" because that is the only way you could think of to solve your
> >problem you will rarely get the answer you REALLY NEED. Better to
> >post your problem and let use suggest the best solution(s). Think
> >how good it will feel when The Clown recommends doing just what you
> >had decided to do!
>

> You mean people should THINK outside the box?

It would be productive, but I more meant: Don't create new boxes
before you know what's going to have to be contained within.

Art S. Kagel

Joerg Spilker

unread,

Jul 22, 2000, 3:00:00 AM7/22/00

to

"Art S. Kagel" wrote:

Hello Art,

> If you REALLY need to disable them then take Byrd's version but if
> what you REALLY need is to be able to load complex corellated data
> without worrying about the order in which you update the tables, use
> Query Gurl's suggestion and DEFER constraint checking until COMMIT
> time.

what i want to do is just this: I´m managing and updating values in
parameter data tables on some evaluation database. After having tested
programs and data on the evaluation machine, i wan´t to transfer the
"new" parameter data to our production database. The data in the
parameter tables have only changed in non primary key values or new
primary keys are added. Now i just want to load this new data in the
target table with a simple DELETE FROM table and then LOAD FROM FILE
INSERT INTO TABLE.

Greetings, Joerg.