16 December 2006

On Constraints In Oracle : deferrable Initially deferred enable disable

You can create constraints with some additional properties in Oracle. For instance you can specify constraint deferrable initially deferred to check constraint when commit done. And also you can disable constraints to make data loading easily-especially ─▒n datawarehouses-.
Demonstration below describes mentioned properties:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> drop table t;
Table dropped
SQL> create table t(a number, z number);
Table created
SQL> alter table t add constraint unique_t unique(a) deferrable initially deferred;
Table altered
SQL> insert into t values(1,1);
1 row inserted
SQL> insert into t values(1,1);
1 row inserted
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1          1
         1          1

SQL> commit;
commit
ORA-02091: transaction rolled back
ORA-00001: unique constraint (HR.UNIQUE_T) violated

SQL> SELECT * FROM t;
         A          Z
---------- ----------

SQL> alter session set constraints = deferred;
Session altered
SQL> alter table t disable constraint unique_t cascade;
Table altered
SQL> insert into t values(1,1);
1 row inserted
SQL> insert into t values(1,1);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM t;
         A          Z
---------- ----------
         1          1
         1          1

SQL> alter table t enable constraint unique_t;
alter table t enable constraint unique_t
ORA-02299: cannot validate (HR.UNIQUE_T) - duplicate keys found
SQL> --other
SQL> --set constraints unique_t deferred;--session
SQL> --alter table t drop constraint unique_t;
SQL> --SELECT * FROM user_constraints;
SQL> --SELECT * FROM user_cons_columns;

No comments: