How to delete a row which is having a Foreign Key?

Question

Here i am having 3 tables named t1, t2, t3

id    dept_name

201      CSE
202      ECE
203      IT
204      MECH
205      EEE
   **T1**

id   dept_per

201     50
202     60
203     70
204     80
205     80
  **T2**

id     dept_rank
 201     2
 202     4
 203     1
 204     5
 205     3
   **T3**

Here Id is the primary key and the T2 & T3 have its foriegn Key.Now i want to delete a single row in the table T3 but it is not allowing me to delete that row because of the foreign key. How can i delete that row without removing a foreign key.

If you deleting a row that has a relationship. You need to remove the relationship before you delete the primary key. — yesemsanthoshkumar, Sep 26 '17 at 12:50
@Sami So using this nocheck constraint all we can disable all constraints in the database right? — Jerold Joel, Sep 26 '17 at 12:59
Possible duplicate of [Delete data with foreign key in SQL Server table](https://stackoverflow.com/questions/8251146/delete-data-with-foreign-key-in-sql-server-table) — Ilyes, Sep 26 '17 at 13:02
@Sami Just check the answer i posted below is correct right?? — Jerold Joel, Sep 26 '17 at 13:04
@JeroldJoel If your answer is right, then your question is duplicate :) — Ilyes, Sep 26 '17 at 13:06

score 0 · Answer 1 · edited Sep 26 '17 at 13:21

I got this answer from the old post just now

How can foreign key constraints be temporarily disabled using T-SQL?

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", 
     @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

How to delete a row which is having a Foreign Key?

1 Answers1