RE: Constraints question
Ray Cruz <cruzr@prodigy.net> wrote:
} Michel.Auger@cgi.ca wrote:
} >
} > Let's say I'm using an "order_header" and "order_item" tables. There =
is a
} > constraint between these tables.
} >
} > I need to unload the "order_header" from a database and load it into =
another
} > database without deleting the "order_item" (the primary keys will not =
changed).
} >
} > If I use this code : begin work;
} > delete from order_header;
} > load from order_header.unl
} > insert into order_header;
} > commit work;
} >
} > Is there a way to have Informix checked the constraints at the =
"commit"
} > instead than the "delete"?
} >
} > Thank you
}
} You can disable the constraint just before the delete. You may then set
} the constraint mode to filtering after loading the "order_header".
} Violations will be captured to your "order_header_vio" table.
}
Presuming you are using OnLine (with logging), the exact function you =
require is the 'Transaction Mode' format of the SET command:
BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED;
DELETE FROM order_header;
LOAD FROM "order_header.unl"
INSERT INTO order_header;COMMIT WORK;
The deferred checking reverts to statement level checking upon a ROLLBACK =
or COMMIT WORK.
cheers
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus Communications |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+
------------------ RFC822 Header Follows ------------------
}Received: by yes.optus.com.au with SMTP;14 Dec 1997 17:55:21 +1000
}Received: from news.optus.com.au ([203.13.126.11])
} by prairiedog.optus.com.au (Netscape Messaging Server 3.1b)
} with ESMTP id AAA35E2; Sun, 14 Dec 1997 17:52:24 +1100
}Received: from happy.optus.com.au (firewall-user@happy.optus.com.au =
}[203.13.126.9]) by news.optus.com.au (8.8.8/8.8.3) with ESMTP id RAA15276; =
}Sun, 14 Dec 1997 17:52:24 +1100
}Received: by happy.optus.com.au; id RAA04889; Sun, 14 Dec 1997 17:52:59 =
}+1100 (EST)
}Received: from rmy.rmy.emory.edu(170.140.97.4) by happy.optus.com.au via =
}smap (3.2)
} id xma004870; Sun, 14 Dec 97 17:52:31 +1100
}Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id =
}AAA29104 for informix-list-out; Sun, 14 Dec 1997 00:10:01 -0500 (EST)
}
}Message-Id: <349359EE.2BED@prodigy.net>
}Subject: Re: Constraints question
}Date: Sat, 13 Dec 1997 23:00:46 -0500
}Reply-To: cruzr@prodigy.net
}Organization: Prodigy Services Corp
}Sender: informix-list-owner@rmy.emory.edu
}To: informix-list@rmy.emory.edu
}X-Informix-List-Id: <news.46600>