Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: truncate problem

Re: truncate problem

From: kev <kevin.porter_at_fast.no>
Date: Wed, 27 Oct 1999 18:10:49 +0100
Message-ID: <38173219.802175B1@fast.no>


OK, thanks for this.
Unfortunately, the "select a.constraint_Name..." query returned an empty result set too.
Perhaps a different user owns that constraint. How do I find out what user the constraint belongs to?

thanks,

rtproffitt_at_my-deja.com wrote:

> 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 - 12:10:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US