Re: Turning Referential Integrity on/off

Alan Popiel (alan@po.den.mmc.com) wrote: : -> [ stuff deleted ] : -> : ->I wonder if there is a way of turning referential integrity on and off. : -> : ->create table cust_calls : -> ( : -> ..... : -> 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 dropping it : ->once it has been defined. I'm in the process of defining a database and : ->too much referetial integrity is an obstacle when trying to load the : ->database but handy during test and in a live environment. : -> : You need to use the ALTER TABLE ... { ADD | DROP } CONSTRAINT ... commands. : This will also encourage you to specify names for your constraints, which : is a good idea, IMHO. : Define your table(s) without any constraints, or maybe only UNIQUE : constraints. Do your data loading; then add your constraints. This has : [ stuff deleted ] : : Regards, : Alan Another possibility is to use the command SET CONSTRAINTS constraint_name, ... DEFERRED. Then constraint checking is turned off and the specified constraints are not checked untill the transaction (which loads your table(s)) is committed. If errors occur, the transaction is rolled back (only possible in a database with logging). Regards,