Re: truncate problem

From: <rtproffitt_at_my-deja.com>
Date: Wed, 27 Oct 1999 16:31:52 GMT
Message-ID: <7v79do$5j8$1@nnrp1.deja.com>

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

---

create table bob1
 ( key1 varchar2(10) not null primary key,    field1 varchar2(10));

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

---

RT>select * from user_constraints
  2 where table_name = 'BOB2';

• does not show the foreign key 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 BOB2

FIELD1 IS NOT NULL IMMEDIATE VALIDATED GENERATED NAME 27-OCT-99

RTPROFFITT                     SYS_C003907                    P BOB2


IMMEDIATE VALIDATED     GENERATED NAME     27-OCT-99

PROPER SEARCH

---

  1 select a.constraint_Name, a.table_name   2 from user_constraints a, user_constraints b   3 where a.r_constraint_name = b.constraint_name   4* and b.table_name = 'BOB2'
*** Finds the constraint on table BOB2

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                C

TABLE_NAME

------------------------------ ------------------------------ -

---

SEARCH_CONDITION

---

---

R_OWNER                        R_CONSTRAINT_NAME              DELETE_RU

STATUS

------------------------------ ------------------------------ ---------
-------
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

---

RT>
RT>alter table bob1 disable constraint sys_c003908;

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