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.

"ERD" <erd (AT) nospam (DOT) net> wrote

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

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@Xenquery .com> wrote in
news:tvGdnX_CI_Oj6cHcRVn-sA (AT) comcast (DOT) com:

"ERD" <erd (AT) nospam (DOT) net> wrote

not at all -- its not abnormal to need to use cascade constraints option if
FKs still exist, and its not abnormal if a table in another schema has FK
constraints

did you find out which table has the FK before you used the 'cascade
constraints' option?

++ mcs

I did through re-running the original scripts. Many thanks



I ran a SELECT object_name FROM user_objects; and came up with, among
other things,