Home » SQL & PL/SQL » SQL & PL/SQL » Deleting all child table records with single DELETE statement (Oracle 10g, XP)
Deleting all child table records with single DELETE statement [message #378350] Tue, 30 December 2008 02:16 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello all,

I have one master table and more than 5 child tables related to that.
In create <child> table statement, I am allowed to add ON DELETE CASCADE constraint.
But now I have to delete one record from master table and all related child table records in one go.
Is it possible?
please help.

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378352 is a reply to message #378350] Tue, 30 December 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, if you have a foreign key.

Regards
Michel
Re: Deleting all child table records with single DELETE statement [message #378354 is a reply to message #378350] Tue, 30 December 2008 02:22 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Sure if you create the parent table with delete cascade option , with the correct foreign keys to the child tables.
Re: Deleting all child table records with single DELETE statement [message #378355 is a reply to message #378352] Tue, 30 December 2008 02:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes sir,

In all child table foreign key is specified. but not ON DELETE CASCADE.

So how to do that?

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378356 is a reply to message #378350] Tue, 30 December 2008 02:25 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, if you have ON DELETE CASCADE on your child tables and you delete from the master table, related rows will automatically be removed from the child tables. Try this example:

CREATE TABLE parent (id NUMBER PRIMARY KEY);
CREATE TABLE child  (id NUMBER PRIMARY KEY,
                    pid REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1);
INSERT INTO child VALUES (1, 1);
DELETE FROM parent WHERE id = 1;
SELECT * FROM child;
Re: Deleting all child table records with single DELETE statement [message #378357 is a reply to message #378356] Tue, 30 December 2008 02:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
That is what I was saying,
all child tables are already exist with foreign key specified within it. But ON DELETE CASCADE constraint is not specified with that.
And now I have to delete all child records with one master table record. And I require help in that regard.

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378361 is a reply to message #378357] Tue, 30 December 2008 02:47 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Just add it. Example:

ALTER TABLE child ADD CONSTRAINT fk_parent
 FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE;

Re: Deleting all child table records with single DELETE statement [message #378366 is a reply to message #378361] Tue, 30 December 2008 03:02 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Frank sir,

As I said earlier, I am not allowed to ALTER any table.
Using single delete statement on master table, I have to delete all related records from child table also.

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378368 is a reply to message #378366] Tue, 30 December 2008 03:04 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, what can you do? Can you write a trigger on the master table?
Re: Deleting all child table records with single DELETE statement [message #378373 is a reply to message #378368] Tue, 30 December 2008 03:09 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
If I will write simple trigger on master table, I would get Mutating table error.

Using three trigger, I can solve this problem.

But I want to delete records, using single DELETE statement, if possible.

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378374 is a reply to message #378373] Tue, 30 December 2008 03:16 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
So, what is wrong with multiple statements? Too much typing or what? If so, write a stored procedure and add the required statements to it.
Re: Deleting all child table records with single DELETE statement [message #378377 is a reply to message #378366] Tue, 30 December 2008 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not allowed to ALTER any table.

Drop and recreate the constraint.
If you are not allowed to do this then ask to be allowed or ask the one that is allowed to create the constraint for you.
Any other behaviour is stupid.

Regards
Michel
Re: Deleting all child table records with single DELETE statement [message #378378 is a reply to message #378374] Tue, 30 December 2008 03:23 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
That means I cant delete all those records in one go. Is it so?

And Frank sir, I didn't get your idea of procedure.
Quote:

write a stored procedure and add the required statements to it.



regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378381 is a reply to message #378378] Tue, 30 December 2008 03:29 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

You can only delete from one table with a single delete statement.

If you're not allowed to alter the table, write a PL/SQL procedure(as already suggested by Frank),since it may contain multiple delete statments.
Re: Deleting all child table records with single DELETE statement [message #378382 is a reply to message #378378] Tue, 30 December 2008 03:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
What makes you think you would get a mutating error or that you would need three triggers? I have expanded Frank's example below to demonstrate trigger usage. You can add one delete statement for each child table to the same single trigger.

-- if you already have this:
SCOTT@orcl_11g> CREATE TABLE parent
  2    (id NUMBER PRIMARY KEY);

Table created.

SCOTT@orcl_11g> CREATE TABLE child
  2    (id  NUMBER PRIMARY KEY,
  3  	pid NUMBER,
  4  	CONSTRAINT pid_fk FOREIGN KEY (pid)
  5  			  REFERENCES parent (id));

Table created.

SCOTT@orcl_11g> INSERT INTO parent VALUES (1);

1 row created.

SCOTT@orcl_11g> INSERT INTO child VALUES (1, 1);

1 row created.

SCOTT@orcl_11g> COMMIT;

Commit complete.

SCOTT@orcl_11g> SELECT * FROM child;

        ID        PID
---------- ----------
         1          1


-- then add this:
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER delete_cascade
  2    AFTER DELETE ON parent
  3    FOR EACH ROW
  4  BEGIN
  5    DELETE FROM child
  6    WHERE  pid = :OLD.id;
  7  END delete_cascade;
  8  /

Trigger created.


-- so you get this:
SCOTT@orcl_11g> DELETE FROM parent WHERE id = 1;

1 row deleted.

SCOTT@orcl_11g> SELECT * FROM child;

no rows selected

SCOTT@orcl_11g> 


This method is even documented:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHJHEBF

However, just to be clear, dropping and recreating the constraint, so that you can make it on delete cascade, is the best method.

[Updated on: Tue, 30 December 2008 03:35]

Report message to a moderator

Re: Deleting all child table records with single DELETE statement [message #378388 is a reply to message #378382] Tue, 30 December 2008 03:57 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Ok, I got it.
Thanks to all.

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378423 is a reply to message #378382] Tue, 30 December 2008 06:46 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Sorry for disturbing again.

The Solution provided by Barbara Mam works perfectly. But it raises one question to me.

Can't we say that using trigger, we can violate foreign key constraint?

And please tell me that, at what time this foreign key constraint is checked by Oracle?
before deleting the record? or after deleting?

regards,
Delna
Re: Deleting all child table records with single DELETE statement [message #378437 is a reply to message #378423] Tue, 30 December 2008 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The Solution provided by Barbara Mam works perfectly.

However, it is NOT the correct way to do it in Oracle.

Regards
Michel

[Updated on: Tue, 30 December 2008 07:56]

Report message to a moderator

Re: Deleting all child table records with single DELETE statement [message #378497 is a reply to message #378423] Tue, 30 December 2008 14:43 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Apparently it checks it after the trigger fires. However, the most appropriate method in Oracle is to drop and recreate your foreign key constraint adding ON DELETE CASCADE to it, as shown below.

-- if you already have this:
SCOTT@orcl_11g> CREATE TABLE parent
  2    (id NUMBER PRIMARY KEY);

Table created.

SCOTT@orcl_11g> CREATE TABLE child
  2    (id  NUMBER PRIMARY KEY,
  3  	pid NUMBER,
  4  	CONSTRAINT pid_fk FOREIGN KEY (pid)
  5  			  REFERENCES parent (id));

Table created.

SCOTT@orcl_11g> INSERT INTO parent VALUES (1);

1 row created.

SCOTT@orcl_11g> INSERT INTO child VALUES (1, 1);

1 row created.

SCOTT@orcl_11g> COMMIT;

Commit complete.

SCOTT@orcl_11g> SELECT * FROM child;

        ID        PID
---------- ----------
         1          1


-- then do this:
SCOTT@orcl_11g> ALTER TABLE child DROP CONSTRAINT pid_fk
  2  /

Table altered.

SCOTT@orcl_11g> ALTER TABLE child ADD CONSTRAINT pid_fk
  2  FOREIGN KEY (pid) REFERENCES parent (id)
  3  ON DELETE CASCADE
  4  /

Table altered.


-- so you get this:
SCOTT@orcl_11g> DELETE FROM parent WHERE id = 1;

1 row deleted.

SCOTT@orcl_11g> SELECT * FROM child;

no rows selected

SCOTT@orcl_11g> 

Previous Topic: Selecting records which dont have referencing records
Next Topic: Learning
Goto Forum:
  


Current Time: Sun Dec 04 08:39:30 CST 2016

Total time taken to generate the page: 0.07237 seconds