By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,639 Members | 1,988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,639 IT Pros & Developers. It's quick & easy.

truncate a table ignoring relations

chathura86
100+
P: 227
hi,
i have a table which has child rows in another table. Now i want to truncate this
table temporally, because i will re-enter those values in a short time with some changes. cannot use update statement to make those changes. only possible way is truncating and inserting.

also i want to insert some data in to child rows without any parent rows. as in the previous section i can add the relevant parent rows shortly.

its like i want to ignore all the relations temporally while Iím performing this operation. After finishing it relations should work properly. it is 100% sure that the data is in the correct order to perform relations after i complete this operation.

Thank you,

Chathura Bamunusinghe
Oct 11 '07 #1
Share this Question
Share on Google+
3 Replies


amitpatel66
Expert 100+
P: 2,367
hi,
i have a table which has child rows in another table. Now i want to truncate this
table temporally, because i will re-enter those values in a short time with some changes. cannot use update statement to make those changes. only possible way is truncating and inserting.

also i want to insert some data in to child rows without any parent rows. as in the previous section i can add the relevant parent rows shortly.

its like i want to ignore all the relations temporally while Iím performing this operation. After finishing it relations should work properly. it is 100% sure that the data is in the correct order to perform relations after i complete this operation.

Thank you,

Chathura Bamunusinghe
In case of primary key - foreign key relationship, you can disable the primary key of the table with CASCADE options. The cascase option will disable the refrential integrity constraints also. After disabling the constraints, you can perfomr TRUNCATE or DELETE/INSERT operations. Then you can ENABLE the constraints again

For disabling and enabling a constraint:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER TABLE table_name DISABLE PRIMARY KEY CASCADE;
  3.  
  4. ALTER TABLE table_name ENABLE PRIMARY KEY;
  5.  
  6.  
Oct 11 '07 #2

chathura86
100+
P: 227
In case of primary key - foreign key relationship, you can disable the primary key of the table with CASCADE options. The cascase option will disable the refrential integrity constraints also. After disabling the constraints, you can perfomr TRUNCATE or DELETE/INSERT operations. Then you can ENABLE the constraints again

For disabling and enabling a constraint:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ALTER TABLE table_name DISABLE PRIMARY KEY CASCADE;
  3.  
  4. ALTER TABLE table_name ENABLE PRIMARY KEY;
  5.  
  6.  
it was a good solution,
thanks a lot.

chathura bamunusinghe
Oct 17 '07 #3

amitpatel66
Expert 100+
P: 2,367
it was a good solution,
thanks a lot.

chathura bamunusinghe
Hi,

You are Welcome!!
DO POST any issues in future, we will help you out!!

Regards,
Amit
Oct 17 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.