Re: Newbie question

"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 - 23:01:10 CEST