Avatar of sam2929
sam2929
 asked on

ORA-02449 error

Trying to drop a table

drop table test

getting
ORA-02449 error

unique/primary keys in table refrenced by foreign keys

How can i fund all the refrences for test1 and then want to disable them.
Oracle Database

Avatar of undefined
Last Comment
johnsone

8/22/2022 - Mon
johnsone

You cannot disable them, you have to drop them.

This query should get you the names of the constraints that are referencing the primary key.

SELECT constraint_owner, 
       constraint_name, 
       table_name 
FROM   dba_constraints 
WHERE  ( r_constraint_owner, r_constraint_name ) IN (SELECT constraint_name, 
                                                            constraint_owner 
                                                     FROM   dba_constraints 
                                                     WHERE 
              constraint_type IN ( 'P', 'U' ) 
              AND table_name = 'TEST'); 

Open in new window

slightwv (䄆 Netminder)

I would just generate the DDL and look at the constraints:
select dbms_metadata.get_ddl('TABLE','TEST') from dual;
sam2929

ASKER
select dbms_metadata.get_ddl('TABLE','Currency_Type_Dim') from dual;

i am getting ORA-31603 and ora-06512 error and yes table do exist there
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

Objects in Oracle are converted to UPPER case:
select dbms_metadata.get_ddl('TABLE','CURRENCY_TYPE_DIM') from dual;
slightwv (䄆 Netminder)

If this isn't a 'test' table, are you sure you want to drop it if there are constraints on it?

If you recreate it, you will need to rebuild all the constraints to make sure everything is back to the way it was.
sam2929

ASKER
i don't want to drop any constraint all i want is to see all constraints related to that table in other tables
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gerwin Jansen

If this is just a test system you may consider dropping the constraints as well:

drop table test cascade constraints;

(be careful)

<edit>

Never mind, did not see your last post as I posted this one.
slightwv (䄆 Netminder)

Then use the SQL posted by johnsone.  I didn't run it but it looks good to me.
sam2929

ASKER
select dbms_metadata.get_ddl('TABLE','CURRENCY_TYPE_DIM') from dual;

i get no result but i know this table have constraints to fact tables
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sam2929

ASKER
SELECT constraint_name
                                                          --constraint_owner
                                                     FROM   dba_constraints
                                                     WHERE
              constraint_type IN ( 'P', 'U' )
              AND table_name = 'Currency_Type_Dim'

no result it dn't like   --constraint_owner
slightwv (䄆 Netminder)

>>i get no result but i know this table have constraints to fact tables

That just generates the DDL for the TABLE.  It would have constraints to other tables not what constraints on other tables have to it.

I guess I missed the actual question here.

Use johnsone's SQL.
slightwv (䄆 Netminder)

>>AND table_name = 'Currency_Type_Dim'

Again:  OBJECT_NAMES in UPPER case.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question