0

I have related tables in SQL (one to many). One is employees table and the other is the record table for day offs, When i want to delete one employee I get the below error in C#:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Izin_Personel". The conflict occurred in database "Inotra", table "dbo.Izin", column 'personelid'.
The statement has been terminated.

I tried changing insert and update actions to cascade and that worked for other tables, but it also deletes from both tables when I delete from one.

So my question is: How can I remove from only one of the two related tables, so that the records of other table remain unchanged?

Alessio Cantarella
  • 5,077
  • 3
  • 27
  • 34
Mert
  • 17
  • 8
  • What are you trying to delete? Employee or the day off record? – dropoutcoder Sep 08 '19 at 16:05
  • I want to delete an employee. But when i delete an employee, i also want to keep their records. Those are the ones i dont want deleted. Employee is primary table and record table is foreign. (Employee=one- many=Record) – Mert Sep 08 '19 at 16:08
  • 1
    Make the relationship optional/nullable. In that case reference to Employee will be set to null instead of deleting the entity(ies) in day off table. – dropoutcoder Sep 08 '19 at 16:13
  • https://stackoverflow.com/questions/3776269/i-got-error-the-delete-statement-conflicted-with-the-reference-constraint – rprakash Sep 08 '19 at 16:14
  • 1
    I think this is a really bad idea. Don't do it. It's better to "disable" the employee, but keep it for the records... forever. Disk space is cheap these days. – The Impaler Sep 08 '19 at 16:16
  • @TheImpaler, of course adding a `status` column to the `employee` table is superior to deleting records - especially for such an example, where even the largest employers in the world don't have more than 3M employees. However, since this solution would require adding an additional qualifier to almost any query (`WHERE employee.status = 'Active'`), in some cases, deleting records, or at least moving them to an archive table, is preferred - and requires deleting other records first (or having the _very dangerous_ `ON DELETE CASCADE` enabled for the foreign keys). – daShier Sep 08 '19 at 16:28
  • Why shouldnt I set it to null? Will there be any consequences? – Mert Sep 08 '19 at 17:42

2 Answers2

1

When you delete from the employee table, then you must also delete all related records in the record table.

SQL Police
  • 4,127
  • 1
  • 25
  • 54
  • Ok so i want to keep the records even after when i delete that related employee. Is there any way that i can do that? – Mert Sep 08 '19 at 16:06
  • Yes. making the relationship between Day off and Employee optional/nullable. Anyway, you'll be facing other issues in that case. – dropoutcoder Sep 08 '19 at 16:12
  • 2
    Thoug possible, I think this is a really bad idea. Just keep the employee rows, and mark them as "inactive" or something. Don't delete them. You may need them in the future for a lot of things. – The Impaler Sep 08 '19 at 16:17
  • I have another table for the employees that i delete from employee table. But if that employee has a day off record i get an error trying to delete. I think that optionak/nullable solution will work. But why is that a bad idea? – Mert Sep 08 '19 at 16:52
  • So you have an Emp table and a Vac table, with a foreign key relationship on empID. An employee leaves and you want him to be removed from the Emp table, but still record his days off in Vac. If you remove the Emp record, the associated Vac records are now orphans. How do you track to whom those records belong. Instead take @theimpaler's suggestion and mark the Emp.record as inactive/terminated – Flydog57 Sep 08 '19 at 17:31
-2

Relationships ensure data integrity, without them, data may be incomplete or incorrect. In your case you need to remove the relationship between the tables("FK_Izin_Personel"), then there will be no error.

Another Mi
  • 12
  • 2
  • 1
    I am questioning the usefulness of relationships right now. What did they provide me? And what possible outcomes could occur when i remove my relationships? – Mert Sep 08 '19 at 17:03
  • 1
    I think this is not a good answer. Removing the relationship between these two tables is destroying the whole sense of a relational database. – SQL Police Sep 08 '19 at 21:04
  • @SQLGeorge can i do what i want not deleting the relationships? And what could happen if i delete them? – Mert Sep 09 '19 at 14:11
  • Database normalization is the cornerstone of database theory. From wiki: "Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity" (https://en.wikipedia.org/wiki/Database_normalization). Once a database is normalized, relationships between the data in multiple tables must be established. But depending on the task... – Another Mi Sep 09 '19 at 16:10