3 Replies Latest reply: Mar 28, 2013 9:23 AM by 367852 RSS

    disable all constraints

      My table has many constraints, but I want to load data into this table, so I have to disable constraints, then do we have ONE command to disable ALL constraints , instead of one-by-one ?

        • 1. Re: disable all constraints
          There is no such command, for obvious reasons.
          Also best practice is not to load directly in the target table, but instead use a staging table without constraints, clean up the staging table, and then copy to the target table.
          Disabling all constraints could also cause big trouble if you are not the only one performing DML on that table.

          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: disable all constraints
            No there is no universal constraint disablement. What you can do is loop round the data dictionary view and use dynamic SQL:
                 for all_cons in ( select table_name, constraint_name from user_constraints
                                      where constraint_type in ('U', 'P', 'R') )
                      execute immediate 'alter table '||all_cons||' disable constraint '||all_cons.constraint_name;
                end loop;
            Note that this only does the relational constraints. Remove the filter on CONSTRAINT_TYPE if you want to include check constraints as well.


            I whole-heartedly endorse Sybrand's warnings. You need to ensure you are the only user of the database when you do this loading.

            Cheers, APC

            Edited by: APC on Mar 28, 2013 1:59 PM
            • 3. Re: disable all constraints
              In addition to the suggestion made by Sybrand, you would have a problem to "clean up" the new data which has been directly added to the table(instead of using a staging table) if the constraints fail to get enabled later. MORE the data BIGGER the cleanup !