0

There are two tables:

enter image description here

In the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is a manager identifier and refers to EMPLOYEE_ID. DEPT_ID is the foreign key to the DEPARTMENT_ID column of the DEPARTMENTS table.

In the DEPARTMENTS table, DEPARTMENT_ID is the primary key.

When I execute the following command 

DELETE
FROM departments
WHERE department_id = 40;

I get this result

0 rows affect

My foreign key have referential integrity is limited - ON DELETE CASCADE . Why DELETE does not be cascade? Does it matter what MGR_ID is the refers to EMPLOYEE_ID column of this table?

Володимир LivePege
  • 11
  • 3
  • 1
    If the foreign key is proper setup, then you cannot delete from `DEPARTMENTS` while there are rows in `EMPLOYEES` with that ID. That is normal and it saved you from corrupting your database. So what is your question actually ? – GuidoG Mar 21 '19 at 08:47
  • Did you got an error like this one `The DELETE statement conflicted with the REFERENCE constraint "ForeignKey_Name". The conflict occurred in database "YourDataBase", table "EMPLOYEES", column 'MGR_ID'` ? – GuidoG Mar 21 '19 at 08:51
  • Hi, GuidoG. No, I not get errore. While working in Microsoft SQL Server Management Studio, I get only the result: 0 rows affect – Володимир LivePege Mar 21 '19 at 08:59
  • 1
    0 rows affected means that it did't find any row to delete, the foreign key would throw the error that @GuidoG mentioned... Or your data is different or you have an instead of delete trigger in the middle – Daniel Brughera Mar 21 '19 at 09:35
  • Does your departments table have any triggers? – SMor Mar 21 '19 at 12:39
  • Can you show us the table schemas, including the foreign key constraint ? Its hard to say without seeing how the keys where setup. Also show any triggers on this tables – GuidoG Mar 21 '19 at 14:06
  • Is EMPLOYEES.MGR_ID a foreign key to EMPLOYEES.EMPLOYEE_ID? and, if so, do you have ON DELETE CASCADE set up there? Because, if you only have it on the DEPARTMENTS.DEPARTMENT_ID to EMPLOYEES.DEPT_ID, it'll get blocked when it tries to delete Bob on the first cascade. – Chris Steele Mar 21 '19 at 15:03
  • 1
    By the way, though I don't think you'd have a problem with this particular schema (just not hiring and firing that frequently, I'd suspect) don't use ON DELETE CASCADE. If there is any level of volume, it'll create locking situations that are difficult to diagnose, and the potential for fat-finger errors exponentially increases due to the amount of side effects that you can't really see from the query window. – Chris Steele Mar 21 '19 at 15:05

3 Answers3

0

You can't delete it because it has dependencies to the primary key, if you'd like to still continue you either have to break the dependencies or delete the dependencies in the query as well.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Arasto
  • 471
  • 6
  • 25
  • Hi, Arasto Sahbaei. If on the constraint on column MGR_ID is ON DELETE CASCADE. Does this not affect the result? Does it matter what DEPT_ID is the foreign key to the DEPARTMENT_ID column of this table? – Володимир LivePege Mar 21 '19 at 09:06
  • https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths?rq=1 – Arasto Mar 21 '19 at 09:10
  • Never ever break dependencies !!! They are there for a reason. In this case it saved him from corrupting his database – GuidoG Mar 21 '19 at 09:30
0

If your table Employees has the statement similar to this:

CONSTRAINT Dept_ID_FK__Departments FOREIGN KEY (department_id ) 
    REFERENCES dbo.Departments(department_id )

It means you cannot delete like you've written, because SQL Server will not allow you to delete the rows with department_id = 40:

DELETE
FROM departments
WHERE department_id = 40;

As there are rows in Employee table with DeptId which are linked to Departments table. As it will get the data into the corrupt state.

StepUp
  • 36,391
  • 15
  • 88
  • 148
0

What happend is simple referential integrety that stepped up, and your delete statement is correctly blocked by the database.

Suppose you where able to delete the row in DEPARTMENTS with Department_ID = 40
Then in your table EMPLOYEES there would be at least one row that points to a department that does not exist !!!
In other words, your database would be corrupt.
That is the purpose of foreign keys, to make sure there is no way at all to corrupt your database like that.

The only way to delete this row from DEPARTMENTS is to first uncouple all rows in EMPLOYEES from that Department_ID.

So, either you couple all employees first to a different department 

update EMPLOYEE
Set    Dept_ID = 20 -- or some other...
where  Dept_ID = 40

or you delete all employees

delete from EMPLOYEE where Dept_ID = 40

And now you can delete your Department

But I still dont understand why you did not get an errormessage...

GuidoG
  • 11,359
  • 6
  • 44
  • 79