Home » SQL & PL/SQL » SQL & PL/SQL » Truncate Master Table Issue (General - All)
Truncate Master Table Issue [message #584769] Fri, 17 May 2013 09:32 Go to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.


1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully..
2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table)
3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table)

Thanks
Waheed Hashim
Re: Truncate Master Table Issue [message #584773 is a reply to message #584769] Fri, 17 May 2013 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) No.
2) You can't
3) You can't

Regards
Michel
Re: Truncate Master Table Issue [message #584786 is a reply to message #584773] Fri, 17 May 2013 12:03 Go to previous messageGo to next message
jweingarth1
Messages: 4
Registered: May 2013
Location: minneapolis
Junior Member
I second the first reply.

And why would you want to keep the details around if you get rid of the masters?
Re: Truncate Master Table Issue [message #584858 is a reply to message #584786] Mon, 20 May 2013 00:47 Go to previous messageGo to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
Our main requirement is we want to remove records from master without disabling the constraint.
Re: Truncate Master Table Issue [message #584859 is a reply to message #584858] Mon, 20 May 2013 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then use DELETE.

Regards
Michel
Re: Truncate Master Table Issue [message #584862 is a reply to message #584769] Mon, 20 May 2013 01:27 Go to previous messageGo to next message
prititiwari
Messages: 2
Registered: May 2013
Location: delhi
Junior Member
Yes we can.
By using "ON DELETE CASCADE" we can delete master data without disabling foreign key constraint.


Priti
Re: Truncate Master Table Issue [message #584863 is a reply to message #584862] Mon, 20 May 2013 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even without this option, you can first manually delete the children then the parents.

Regards
Michel
Re: Truncate Master Table Issue [message #584872 is a reply to message #584863] Mon, 20 May 2013 02:20 Go to previous messageGo to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
As we know Delete command is much slow, it can br rollbacked... we want to use TRUNCATE command.

I looked for Truncate Cascade but there is no option like that
Re: Truncate Master Table Issue [message #584874 is a reply to message #584858] Mon, 20 May 2013 02:35 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wah_has wrote on Mon, 20 May 2013 07:47
Our main requirement is we want to remove records from master without disabling the constraint.

Why is that so?
Re: Truncate Master Table Issue [message #584876 is a reply to message #584874] Mon, 20 May 2013 02:46 Go to previous messageGo to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
Its one of our requirement
Re: Truncate Master Table Issue [message #584878 is a reply to message #584876] Mon, 20 May 2013 02:59 Go to previous messageGo to next message
Littlefoot
Messages: 19619
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, so you don't want to DELETE because it is slow and it can be rolled back.
You don't want to disable constraints because of the requirement.
If you don't disable constraints, you can't use TRUNCATE.

So, how exactly do you plan to remove these records? Scissors? Lawn mower? Magic wand?
Re: Truncate Master Table Issue [message #584880 is a reply to message #584878] Mon, 20 May 2013 03:13 Go to previous messageGo to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
So your Final Words...

If you don't disable constraints, you can't use TRUNCATE.

Thanks for this response...
Re: Truncate Master Table Issue [message #584881 is a reply to message #584880] Mon, 20 May 2013 03:32 Go to previous messageGo to next message
Wah_has
Messages: 7
Registered: July 2006
Location: Lahore
Junior Member
any one else for this problem??
Re: Truncate Master Table Issue [message #584882 is a reply to message #584881] Mon, 20 May 2013 03:35 Go to previous messageGo to next message
prititiwari
Messages: 2
Registered: May 2013
Location: delhi
Junior Member
the only thing which u can use here is "ON DELETE CASCADE"..
this way you can delete your master data without disabling constraint or without deleting child records...

Priti
Re: Truncate Master Table Issue [message #584884 is a reply to message #584882] Mon, 20 May 2013 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the question.

Regards
Michel
Re: Truncate Master Table Issue [message #584886 is a reply to message #584881] Mon, 20 May 2013 03:46 Go to previous message
Michel Cadot
Messages: 59142
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wah_has wrote on Mon, 20 May 2013 10:32
any one else for this problem??


You have the DEFINITIVE answer for the question you asked.

Regards
Michel
Previous Topic: Need help on SQL join.
Next Topic: Understanding Primary Key/Composite Key
Goto Forum:
  


Current Time: Fri Sep 19 09:07:02 CDT 2014

Total time taken to generate the page: 0.17860 seconds