March 18, 2011 at 11:04 am
Hello,
i am a issue that i dont know how to solve it.
I have a table that i need to delete the data from...
the problem is it has 130 million rows in it so when i do the delete it just hangs .
i than tired to do the truncate table but it has the PK and FK on it.
so i first delete the FK and than look in SSMS and see the FK is not there anymore.
i than runt the truncate command but it gives me a error.
cannot truncate ojbest becaus it has a Fk constraint.
same thing when i try to drop the table.
what am i doing wrong here.
March 18, 2011 at 12:22 pm
it complains because the table is being refered to by another table.
Drop the FK constraint on the refering table and you'll be ok.
Keep in mind to add the FK after your operation.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 18, 2011 at 12:57 pm
Hello,
thank you for ur fast response.
so if i understand it correctly, the fk on the table needs to be truncate table A ( the original table of that FK) go to the original table table B and delete what ( the PK or the FK)?
let me give a simple example
table A
needs to truncate the table A.
its giving the error of FK constraint.
lest say the name of the FK is FK_constraint.and the FK is from table B
so, i go to table B and look/delete which 1?
in the table B ( i have 1 PK and 4 FK's)
thanks in advance.
March 18, 2011 at 3:44 pm
You can use truncate with any table that ins't being referenced by a dependent table.
However, you dan use "delete from ...", but that may generate a load of log entries.
To be able to use truncate, the table cannot be referenced by any other tables. Hence, if you want to use truncate, you'll have to drop the foreign key constraint pointing to your table.
Keep in mind to put it back once your operation is completed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 19, 2011 at 6:26 am
Plan B.
Script the table with all objects, constraints, triggers, etc.
Drop it and recreate it.
March 20, 2011 at 12:00 pm
hello every one
first of all thank you for all your help, i was able to solve it and woudl just like to let every one know how.
- when i drop the FK A and go and see it in the SSMS it is not there
try to truncate table it says cannot drop because it has FK constraint.
-- cant even drop the table and recreate it because when i try to run the drop command it says the same thing . cannot drop the table becaus eit has FK constarint.
--- funny thing happen , i ran sp_helpdb on the desired table and it tell me all the FK refrenced in the table, it shows another FK lets say FK A:B.
when i try to go and delete that foriegn FK through the script it says syntax error ( no idea why )
-- i cant see that FK A:B on the SSMS only see it when i run sp_helpdb on the table
i recraeted through the script the FK A again on the table and when i open that FK in the properties i see both the keys name there.
remove the names there, than go and delete the FK A from the SSMS and than run the turncate table and wollla it happened.
even i have been able to solve the issue i wuld like to know why it happened the way it did and how come i was not able to see the second FK through the SSMS.
any one's help in this matter is very helpful.
March 20, 2011 at 2:31 pm
You would have to post the scripts for us to be able to interpret the reason of your syntax error.
You cannot truncate tableA if tableB references it (via FK) (or any other tables).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 21, 2011 at 6:04 am
hi try these.....
- when i drop the FK A and go and see it in the SSMS it is not there
try to truncate table it says cannot drop because it has FK constraint.
try using the option while deleting ... ON DELETE CASCADE
before doing that make a note of all the dependents
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply