AtulPadhye
asked on
Unable to truncate a table referenced by other tables using foreign keys
Table A has userid as a primary key, table B has userid as a primary key and references A.userid thru a foreign key, even if both tables are empty, I cannot truncate table A, why ?
I can delete table A by setting 'on delete cascade' option in the foreign key constraint, is
there a similar option for truncate ?
I can delete table A by setting 'on delete cascade' option in the foreign key constraint, is
there a similar option for truncate ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
As Delete handles row by row, it does with cascade option.
Truncate option handles on entire table. You should disable the foreign key constraint and try truncate.
Ashok.
As Delete handles row by row, it does with cascade option.
Truncate option handles on entire table. You should disable the foreign key constraint and try truncate.
Ashok.
By using cascade delete, it's basically remove all child records related to the master row.
Ususally, if I'm trying to clean up both master and detail tables by using 'TRUNCATE' statement, I'll disable the foreign key ('ALTER TABLE XXX disable contraints FK_XXX'). I could then truncate both tables. Afterwards, I could enable the foreign key again.
Hope this helps.