Newbie question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ERD - 30 Sep 2004 21:49 GMT

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.

Reply to this Message

Mark C. Stock - 30 Sep 2004 22:01 GMT

| New to Oracle and having an apparent RI problem in SQL PLus.
|
[quoted text clipped - 33 lines]
|
| 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

Reply to this Message

ERD - 30 Sep 2004 22:10 GMT

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?

>| New to Oracle and having an apparent RI problem in SQL PLus.
>|
[quoted text clipped - 47 lines]
>
> ++ mcs

Reply to this Message

Mark C. Stock - 30 Sep 2004 22:28 GMT

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

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

Reply to this Message

ERD - 30 Sep 2004 23:12 GMT

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,

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

Reply to this Message