CODEWITHSUNDEEP
oracle-databaseplsql
Deepak Sharma
Deepak Sharma

Reputation: 11

Deletion of rows with foreign key constraints

I am working on a requirement.

There is one table.I want to delete a row from this table but i cannot delete it because

1.This table is referred by 79 table through foreign key relationship 2.Each of those 79 tables are referred by other 100's of table

so you can imagine its kind of tree structure.So i need to start from bottom and reach to TOP.I need to do it through SQL

So from where I should start ???Do i need to create a temp table or anything which i can do any approach which can be useful???

Note:CASCADE DELETE NOT ALLOWED

I might not have access to any sys tables.. any approach that can be handy??? I am using oracle 10g

Upvotes: 0

Views: 18713

Answers (2)

Thomas Tschernich
Thomas Tschernich

Reputation: 1282

You could use the following query (that does not need access to sys tables) to get all the Foreign Keys of a table and their corresponding Primary Keys. You could use this to build some kind of recursive function that goes through your tree and deletes all parent entries.

select uc.constraint_name, ucc.table_name, ucc.column_name from
user_constraints uc
left join user_cons_columns ucc on (uc.r_constraint_name = ucc.constraint_name)
where uc.table_name = 'TABLENAME'
and uc.constraint_type = 'R'

Upvotes: 0

Christopher Lates
Christopher Lates

Reputation: 222

See: http://www.techonthenet.com/oracle/foreign_keys/disable.php

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

Just be sure to understand the consequences of doing such a procedure and the effects it will have on other data / application functions.

Also, if it wasn't obvious be sure to enable the constraint afterwards

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

Upvotes: 3

Related Questions