How to temporarily disable referential constraints on a table
Joerg Spilker asked how to disable referential constraints temporarily. Query Gurl suggested SET CONSTRAINTS ALL DEFERRED. Art Kagel said to use that to defer checking until COMMIT if the aim is loading correlated data, or the disable approach if truly needed, and urged posting the underlying problem. Joerg explained he wanted to replace parameter tables with DELETE then LOAD. No final answer was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
SET CONSTRAINTS ALL DEFERRED ~Tisha "Joerg Spilker" <js@jetsys.de> wrote in message news:39771DF8.AA9419CD@jetsys.de... > 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
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 Joerg Spilker wrote: > > 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
"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.