Problem to enable oracle constraints

Question

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.

Why would you disable contraints if the data in the end must respect those constraints? — GolezTrol, Jun 16 '11 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. — Marc-André, Jun 16 '11 at 14:52

Justin Cave · Accepted Answer · 2011-06-16T15:49:03.773

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

edited Jun 16 '11 at 15:49

answered Jun 16 '11 at 14:51

Justin Cave

227,342
24
367
384

Thank you for your fast reply!! So it's impossible because it's DDL? – Marc-André Jun 16 '11 at 15:07
@Marc-Andre - It is impossible to rollback DDL, yes. – Justin Cave Jun 16 '11 at 15:19
@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-André Jun 16 '11 at 15:24
@Marc-Andre - It sounds like you may want to make your constraints deferrable instead of disabling and enabling them. I've updated my answer. – Justin Cave Jun 16 '11 at 15:49
@Justin Cave Oh thanks!! But my constraints are set not deferrable when I try to SET CONSTRAINT FK_01_A1_DOSS_VA DEFERRED; it says thats impossible. And the alter session doesn't work. is it really impossible? – Marc-André Jun 16 '11 at 17:18
@Marc-Andre - The constraints would need to be created as deferrable in order to be able to defer them (note the `DEFERRABLE` keyword in my ALTER TABLE). You could, presumably, recreate your non-deferrable constraints as deferrable constraints. – Justin Cave Jun 16 '11 at 17:20
@Justin Cave oh god! Thanks... the dba asked me something impossible then cause we can't drop tables here. You should be my dba ;) thank you for your great help. I will mark this post solved. Have a nice day – Marc-André Jun 16 '11 at 17:33
@Marc-Andre - you don't need to drop the table, you can drop and re-add the constraints. You may also be able to `alter table modify constraint deferrable` - which presumably you could do in a slightly modified version of @Justin's loop, as a one-off task. Assuming your DBA doesn't object, I suppose. – Alex Poole Jun 16 '11 at 18:12
2

@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. – Justin Cave Jun 16 '11 at 18:27
@Justin Cave - indeed, I stand corrected; 'may' was optimistic. I don't think the docs aren't clear on this, they just say it can't change to 'initially deferred'. Trying to change to deferrable gets ORA_00933 rather than an explicit error. Good to know. – Alex Poole Jun 17 '11 at 07:23

Problem to enable oracle constraints

1 Answers1