4

I'm trying for days to disable the constraints of all my tables, insert data and enable the constraints. It works but I did some tests and when I insert a row that doesnt respect a foreign key, there's no error message when I enable the constraints. All I can see is that that constraint is not validated.

This is the code to disable

begin    
     BEGIN
      FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints WHERE  constraint_type = 'R' OR constraint_type = 'P' )
      LOOP
        BEGIN
        EXECUTE IMMEDIATE 'alter table '||rec.table_name||' disable constraint '||rec.constraint_name;
         EXCEPTION WHEN OTHERS THEN
             dbms_output.put_line( 'ERROR: alter table '||rec.table_name||' DISABLE constraint '||rec.constraint_name||';' );
        END;      
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('CONSTRAINTS DISABLED');
     END;
     BEGIN
     FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' )
      LOOP
            EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' disable';
      END LOOP;
    END;
    DBMS_OUTPUT.PUT_LINE('TRIGGERS DISABLED');
end;    
/

This is the code to enable

begin
  BEGIN
      FOR rec IN ( SELECT constraint_name, table_name FROM user_constraints where status = 'DISABLED' and constraint_type = 'R' OR constraint_type = 'P' )
      LOOP
        BEGIN
          EXECUTE IMMEDIATE 'alter table '||rec.table_name||' enable constraint '||rec.constraint_name;
          dbms_output.put_line('alter table '||rec.table_name||' enable constraint '||rec.constraint_name);
        EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line('ERROR: alter table '||rec.table_name||' enable constraint '||rec.constraint_name||' ;' );

        END;
      END LOOP;
    END;
                DBMS_OUTPUT.PUT_LINE('CONSTRAINTS ENABLED');
    BEGIN
      FOR rec IN ( SELECT trigger_name FROM user_triggers WHERE TRIGGER_NAME NOT LIKE 'BIN$%' )
      LOOP
          EXECUTE IMMEDIATE 'alter trigger '||rec.trigger_name||' enable';
      END LOOP;
    END;
                DBMS_OUTPUT.PUT_LINE('TRIGGERS ENABLED');
END;

I don't know how to check all constraints at the end and do a rollback if it doesn't work.

Improve this question
2
  • Why would you disable contraints if the data in the end must respect those constraints? Commented Jun 16, 2011 at 14:44
  • Because I load lots of xml files with the data and I dont insert them in the good order, it would take too long. Commented Jun 16, 2011 at 14:52

1 Answer 1

Reset to default
6

First off, your code is catching the error that is raised when the constraint cannot be enabled and it is throwing that error away rather than re-raising it. That is almost always a bug. If you don't want to ignore the fact that enabling the constraint failed, you'd want something like

FOR rec IN ( SELECT constraint_name, 
                    table_name 
               FROM user_constraints 
              where status = 'DISABLED' 
                and constraint_type = 'R' 
                 OR constraint_type = 'P' )
LOOP
  BEGIN
    l_sql_stmt := 'alter table '||rec.table_name||
                  ' enable constraint ' || rec.constraint_name;
    dbms_output.put_line( l_sql_stmt );
    EXECUTE IMMEDIATE l_sql_stmt;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('ERROR: ' || l_sql_stmt );
      raise; -- re-raise the exception
  END;
END LOOP;

Because ALTER TABLE is DDL, you cannot rollback. If you encounter an error enabling one constraint, you can't rollback the enabling of all the prior constraints.

Based on your last update, it sounds like you may not want to disable and enable the constraints at all. You may simply want to create deferrable constraints.

SQL> create table foo (
  2    col1 number
  3  );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  alter table foo
  2    add constraint pk_foo
  3        primary key( col1 )
  4*       deferrable
SQL> /

Table altered.

Normally, this behaves like any other primary key constraint

SQL> insert into foo values( 1 );

1 row created.

SQL> insert into foo values( 1 );
insert into foo values( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_FOO) violated


SQL> rollback;

Rollback complete.

But if you defer the constraint, it won't be validated until you commit. And if a constraint is violated when the transaction ends, the transaction is rolled back.

SQL> alter session set constraints = deferred;

Session altered.

SQL> select * from foo;

no rows selected

SQL> insert into foo values( 1 );

1 row created.

SQL> insert into foo values( 1 );

1 row created.

SQL> insert into foo values( 1 );

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.PK_FOO) violated
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you for your fast reply!! So it's impossible because it's DDL?
@Marc-Andre - It is impossible to rollback DDL, yes.
@Justin Cave Thank you for your fast reply!! I insert the data from many xml files but I don't know the order that I will have them, so maybe it won't respect all the contrainst while the script runs but at the end, it should be good cause the xml respect the database contrainst. But it's not safe cause someone could edit the files. Is there something I could do? Thank you
@Marc-Andre - It sounds like you may want to make your constraints deferrable instead of disabling and enabling them. I've updated my answer.
@Alex - I'm pretty sure that you can't alter a non-deferrable constraint to turn it in to a deferrable constraint. Normally, that would require that the underlying index get turned from a unique index to a non-unique index but even if you create the constraint initially and specify that it use a non-unique index, it doesn't appear that you can modify the constraint. You have to drop and re-create the constraint.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.