Total Pageviews

August 27, 2016

8/27/2016 01:47:00 PM


Cascading Truncate 
• When you truncate a parent table with child tables, you get: ORA-02266: unique/primary keys in table referenced by enabled foreign keys 
• In Oracle 12c, you can use: truncate table cascade; 
• Must have defined the FK as ON DELETE CASCADE. 
• Otherwise ORA-14705: unique or primary keys referenced by enabled foreign keys in table will result


Default Values 
SQL> create table test1 (col1 number, col2 number default on null 0); 
Table created. 
SQL> desc test1 
Name    Null?               Type 
----------------- -------- ------ 
COL1                            NUMBER 
COL2 NOT NULL       NUMBER 
SQL> insert into test1 values (1, null); 
SQL> insert into test1 values (2,2); 
SQL> select * from test1; 
COL1 COL2 
 1         0 
 2         2