Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: truncate problem
As user "Schema Owner" do
Select ...from ALL_CONSTRAINTS...
You don't need to be SYS.
but you can find what you want with USER_CONSTRAINTS. You cannot find the constraint on table "tablename" because it is owned by another table... it "points" to "tablename".
Thus you want any constraint whose R_CONSTRAINT_NAME is owned by "tablename". See the examples below. BOB2 is the table you are interested in....
SETUP
Table created.
RT>truncate table bob1;
Table truncated.
create table bob2
(field1 varchar2(10) not null primary key);
Table created.
RT>truncate table bob2;
Table truncated.
RT>alter table bob1 add foreign key (field1) references bob2; Table altered.
RT>truncate table bob1;
Table truncated.
RT>truncate table bob2
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign
keys
CURRENT PROBLEM, can't see constraint
OWNER CONSTRAINT_NAME C TABLE_N ------------------------------ ------------------------------ - -------SEARCH_CONDITION
R_OWNER R_CONSTRAINT_NAME DELETE_RU ------------------------------ ------------------------------ --------- DEFERRED VALIDATED GENERATED BAD LAST_CHAN --------- ------------- -------------- --- --------- RTPROFFITT SYS_C003906 C BOB2FIELD1 IS NOT NULL IMMEDIATE VALIDATED GENERATED NAME 27-OCT-99
RTPROFFITT SYS_C003907 P BOB2 IMMEDIATE VALIDATED GENERATED NAME 27-OCT-99
PROPER SEARCH
RT>/
CONSTRAINT_NAME TABLE_NAME ------------------------------ ------------------------------ SYS_C003908 BOB1
1 row selected.
1 select * from user_constraints where
2* constraint_name = 'SYS_C003908'
RT>/
OWNER CONSTRAINT_NAME CTABLE_NAME
------------------------------ ------------------------------ -
R_OWNER R_CONSTRAINT_NAME DELETE_RUSTATUS
------------------------------ ------------------------------ --------- ------- DEFERRED VALIDATED GENERATED BAD LAST_CHAN --------- ------------- -------------- --- --------- RTPROFFITT SYS_C003908 R BOB1 RTPROFFITT SYS_C003907 NO ACTION ENABLED IMMEDIATE VALIDATED GENERATED NAME 27-OCT-99
1 row selected.
SOLUTION
Table altered.
RT>truncate table bob2;
Table truncated.
RT>alter table bob1 enable constraint sys_c003908;
Table altered.
RT>truncate table bob2;
truncate table bob2
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign
keys
Robert Proffitt
Beckman Coulter
Brea California
RTProffitt "AT" beckman "DOT" com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Oct 27 1999 - 11:31:52 CDT