1

This is my code:

Student student = db.Students.Find(id);

db.Students.Remove(student)
db.SaveChanges();
return RedirectToAction("Index");

I get the following error

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Payment_Student". The conflict occurred in database "BlackBeardDB", table "dbo.Payment", column 'studentID'

The code is not deleting the record. Do I have to use CascadeOnDelete(); for this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ichigo Kurosaki
  • 215
  • 2
  • 9
  • 2
    You cannot remove your student as long as there are payments for that student ..... so you need to **first** delete all the payments for this student, **THEN** delete the student entry. Plain and simple referential integrity 101 .... – marc_s Feb 13 '20 at 07:10
  • but wont i be able to delete them all at one with CascaseOnDelete(); again im not 100% sure? – Ichigo Kurosaki Feb 13 '20 at 07:12
  • 1
    `CascadeOnDelete` is turned `ON` by default in `EF`. – MKR Feb 13 '20 at 07:24

3 Answers3

3

You cannot delete student entity from database, because student is one to many relation to payments. You first delete payments that specifict student after you can delete student.

You can check the following to solve this problem

  1. Hard delete (delete permanently) from database dont't recommended. You can use soft delete pattern Please to look at : Entity Framework Core: Soft Delete

  2. You can try Cascade delete: Cascade Delete

Note: If Cascade delete not working yet, please check database configurations, Below links may help to you

  1. https://www.techonthenet.com/sql_server/foreign_keys/foreign_delete.php
  2. How do I use cascade delete with SQL Server?
Ramil Aliyev 007
  • 4,437
  • 2
  • 31
  • 47
  • `Cascade` is default. So question is why its not working to `OP`. – MKR Feb 13 '20 at 07:29
  • @MKR thanks you for comment. This problem may be due to database configurations. The configuration should be reviewed. – Ramil Aliyev 007 Feb 13 '20 at 07:33
  • 1
    Exactly. Let's find out actual problem and address in answer. Otherwise, I'm afraid answers will confuse future users. OP should check and provide feedback. – MKR Feb 13 '20 at 07:35
  • 1
    Wonderful!! May be you can mention that `CasecadeDelete` is `ON` by default at EF layer. – MKR Feb 13 '20 at 09:04
0

You cant directly delete the element from parent table Students since it is in foreign key relationship with Payment table . First you should delete the entries from the child table viz. here Payment table and then the entry from the student table. OR You can soft delete the entry from Student table where you can add a column IsDeleted and update this bit flag to true when entry is deleted instead of hard-deleting entry directly.

Yadukrishnan M
  • 152
  • 14
0

You can't delete student record before delete the payment details or need to update student id in payment table to some orphan id. So you can use ON DELETE CASCADE or ON UPDATE CASCADE for resolving the issue.

Thanks, Neil

Nilamani
  • 66
  • 4