Delete a table with FK | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Delete a table with FK

I am going to DELETE a table. I tried both DELETE or TRUNCATE. The problem is that my table has a FK constraint. That’s why I get the following error: Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with TABLE REFERENCE constraint ‘FK_tblTQ_tblAccs’. The conflict occurred in database ‘DBTest’, table ‘tblTQ’.
The statement has been terminated. There should be a SETting comand to disable the constraint checking. Would you please let me know. I searched the site for "DELETE TABLE" before I write this request but couldn’t find a respons. Farhad R
[email protected]
I could solve the problem as below: Design Table -> Manage Relationships… -> Then put a tick for "Cascade Delete Related Records" I did this for both tables and then ran the DELETE TABLE command. Then I returned the situation as before. Isn’t there any simpler solution? Farhad R
[email protected]
ALTER TABLE TABLENAME
DROP CONSTRAINT FK__CONSTRAINTNAME
GO
ALTER TABLE TABLENAME
ADD CONSTRAINT FK__CONSTRAINTNAME
FOREIGN KEY (COLUMNNAME)
REFERENCES REF_TABLENAME
ON DELETE CASCADE
GO
DELETE FROM TABLENAME
GO
ALTER TABLE TABLENAME
DROP CONSTRAINT FK__CONSTRAINTNAME
GO
ALTER TABLE TABLENAME
ADD CONSTRAINT FK__CONSTRAINTNAME
FOREIGN KEY (COLUMNNAME)
REFERENCES REF_TABLENAME
ON DELETE NO ACTION you could just script the command and run it. Better then using the GUI
Lazy_DBA, This way I have to know the FK constraint, then rebuild it after truncating the table. This is T-SQL of what I did in GUI. Seems good but I am searching for an easier way and post it on this site. Thank you. Farhad R
[email protected]
I dont think it’s that hard, actually I believe using TSQL is the easiest way. You could run sp_help tblTQ and get the FK relationship name, that easy. Basically once you get the FK_name drop it and call it what you want then save this script and run it when ever you need it to perform this. When I first started out with SQL Server I used EM a lot now I perform most of my duties through scripts. I find it to be a more convienent especially when administering a multi server envriroment. Anyways good luck!
]]>