Re: Newbie question

From: ERD <erd_at_nospam.net>
Date: Thu, 30 Sep 2004 16:10:29 -0500
Message-ID: <Xns9574AEB351DD0glnospamnet@216.196.97.131>

Yes I finally figured out the cascade constraints. Thanks for that select statement.

Question: does this imply that the RI in the relationship was not defined correctly?

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in news:tvGdnX_CI_Oj6cHcRVn-sA_at_comcast.com:

> 
> "ERD" <erd_at_nospam.net> wrote in message
> news:Xns9574AB194D277glnospamnet_at_216.196.97.131...

>| New to Oracle and having an apparent RI problem in SQL PLus.
>|
>| Ran a script to create some tables. When I began to drop them (this is
>| for a class) one would not drop citing an RI constraint.
>|
>| I have a problem with a table in SQL Plus.
>|
>| When I try to drop this table (Product), I get this
>|
>| SQL> drop table Product;
>| drop table Product
>| *
>| ERROR at line 1:
>| ORA-02449: unique/primary keys in table referenced by
>| foreign keys
>|
>| I thought I understood RI, but in this case......there
>| are no other tables still existing. I went through the script that
>| created the tables and checked every reference to this one.
>|
>| Is there a command I can issue to determine what its
>| talking about?
>|
>| A describe yields this
>|
>| SQL> describe Product
>| Name Null? Type
>| ----------------------------------------- --------
>| PROD_NUM NOT NULL NUMBER
>| ARTISTS_NAME VARCHAR2(40)
>| PROD_NAME VARCHAR2(40)
>| PROD_TYPE VARCHAR2(10)
>| YEAR_REL DATE
>|
>| Any thoughts?
>|
>| Thanks in advance.

> 
> SELECT *
>   FROM all_constraints
>  WHERE (r_owner, r_constraint_name) IN (SELECT owner
>                                               ,constraint_name
>                                           FROM user_constraints
>                                          WHERE constraint_type = 'P'
>                                            AND table_name = 'PRODUCT')
> 
> perhaps a table in another schema has an FK reference
> 
> you can always do a 'drop table xxx cascade constraints'
> 
> ++ mcs
> 
> 
> 

Received on Thu Sep 30 2004 - 16:10:29 CDT