Cannot truncate a table which is being referenced by other table

Why can't I truncate a table which is being referenced by other table?
This happens even if the child table does not have any records.
I get ORA-02266.
------------------------------------------------------------------
SQL> create table T1 ( col1 number);

Table created.

SQL> create table T2(col2 number);

Table created.

SQL> alter table T1 add constraint PK1 primary key (col1)
2 /

Table altered.

SQL> alter table t2 add constraint fk2 foreign key (col2) references T1
2 /

Table altered.

SQL> truncate table t1 reuse storage;
truncate table t1 reuse storage
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

You can't truncate a table that has a primary key which is a foreign key in a child table. Drop the relationship first.

No need to drop the foreign key constraint. Just disabling it for the duration of truncate operation and enabling it afterwards will be enough. So in your case:

Code:

---

ALTER TABLE t2 DISABLE CONSTRAINT fk2;

TRUNCATE TABLE t1 REUSE STORAGE; 

ALTER TABLE t2 ENABLE CONSTRAINT fk2;

---

Quote:

---

Originally posted by jmodic
No need to drop the foreign key constraint. Just disabling it for the duration of truncate operation and enabling it afterwards will be enough. So in your case:

Code:

---

ALTER TABLE t2 DISABLE CONSTRAINT fk2;

TRUNCATE TABLE t1 REUSE STORAGE; 

ALTER TABLE t2 ENABLE CONSTRAINT fk2;

---

---

Yes, that's the way it should be done...