Re: Turning Referential Integrity on/off
oli@environ.se ('rjan Lindberg) writes:
>I wonder if there is a way of turning referential integrity on and off.
you need to have transactions and:
BEGIN WORK;
SET CONSTRAINTS ALL DEFERRED;
...
...
...
COMMIT WORK;
Constraints then are not applied until just before the COMMIT WORK.
The only drawback to this is that *all* work required to put the
database back in sync needs to be in the same transaction (which makes
sense; it's just sometimes a pain)
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
>create table cust_calls
> (
> customer_num integer,
> call_dtime datetime year to minute,
> user_id char(18) default user,
> call_code char(1),
> call_descr char(240),
> res_dtime datetime year to minute,
> res_descr char(240),
> primary key (customer_num, call_dtime),
> foreign key (customer_num) references customer (customer_num),
> foreign key (call_code) references call_type (call_code)
> );
>The foregn key statement defines the referential integrity but is there a way of
>defining it after the table has been created and/or droppping it once it has been
>defined. I'm in the process of defining a database and to much referetial integrity
>is an obstacle when trying to load the databas but handy during test and in a live
>environment.
>Orjan Lindberg
>Swedish EPA
>oli@environ.se