This content has been marked as final. Show 12 replies
fame wrote:I think the Table design in incorrect. If you had implemented Foreign Keys (Sounds like you have not from your explanation), a Simple delete from Master table would have deleted all the dependent references.
I have Doubt in Delete a record in tables,
I want to delete a record in one table, but that record reference to one or more tables,
how can i delete those records.
T_A table refer in the T_B table and T_B table refer in the T_C table.
You will have to now:
1. Check for the Depth of Dependency.
2. Pursue a Bottom - Up approach starting with the Table having no dependency and moving to the Top.
If you can now, read the Example of On Delete Constraint and implement it, you can still save ample efforts and improve database design.
AppsLearner wrote:Untrue, unless you have implemented Foreign Keys. Without Foreign Key, you can still delete the Master first and Child Later (Definitely a wrong approach :) )
This is called - Master -details relations or Primary-master key constrains.
You cannot delete data from master while related data exists in details table.
If you want so,Could not make up what you wanted to convey!!!
First delete from master the relevant data , then you can delete from master.
If you want your child records to be deleted automatically you need to define the foreign key constraint as ON DELETE CASCADE
SQL> create table parent (id integer primary key); Table created. SQL> create table child (id integer, constraint child_fk foreign key (id) references parent(id) on delete cascade); Table created. SQL> insert into parent values (1); 1 row created. SQL> insert into child values (1); 1 row created. SQL> select * from parent; ID ---------- 1 SQL> select * from child; ID ---------- 1 SQL> delete from parent; 1 row deleted. SQL> select * from parent; no rows selected SQL> select * from child; no rows selected SQL>
Follow my suggestion then.
Write an entire procedure to perform the job, which can be achieved in a Single Statement in each of your tables. Plus, you will have to maintain and enhance the procedure to accommodate any more extension to the child tables.
Reason for doing this is, You are not informing Oracle about the Relationship between the Tables by not forming the Foreign Key. Hence, it would be base-less to expect an Oracle implementation that shall do the Subjected job for you.
By the way, what prevents you from using the Foreign Key relationships? Is the Master data not a Primary Key? Are the Tables not normalized?
As everyone told, this is like parent-child relationship. You can not delete that parent record without deleting the child records. The other way if you disable that constraint which is a bridge between these two tables then you can delete the parent record without deleting the child records...
*"on delete cascade"* is another option and it deletes the child record automatically, when you delete the parent ones.
Edited by: Bibhuprasad on Aug 7, 2012 3:00 PM
Edited by: Bibhuprasad on Aug 7, 2012 3:01 PM
fame wrote:Can't help you with a ready code now. but a small algorithm to work with.
Can you show example procedure of this one.
1. Loop through all the Tables in Hierarchy
2. Get the Foreign/Mapping Key for Parent and Child Table.
3. Delete the Data of Child.
4. Check if it is the End of Hierarchy. If yes, then Delete the Root (Main Table) tables data. If not Follow Step 2.
I do understand, it is very easy to prepare an Algorithm compared to its implementation. Hence, for this reason I asked you to use the Foreign Key Constraints at the First Place. :)
You still haven't answered by previous few questions.
fame wrote:If I did not have any PK - FK relationship, I would think of using a similar SQL to get the Deepest Hierarchy.
How can i find the last hierarchy of the loop.
select distinct b.column_name, b.table_name, decode ( b.table_name, 'TABLE3', 1, 'TABLE2', 2, 'TABLE1', 3 ) hie from user_constraints a, user_cons_columns b where a.constraint_name = b.constraint_name and a.constraint_type = 'P' and b.table_name in ('TABLE1', 'TABLE2', 'TABLE3') order by decode ( b.table_name, 'TABLE3', 1, 'TABLE2', 2, 'TABLE1', 3 );