how to check before deleting particular department id in department table that that key exist in em

Posted by Haider_Sigma under Sql Server on 7/10/2014 | Points: 10 | Views : 597 | Status : [Member] | Replies : 4
how to check before deleting particular department id in department table
that that key exist in employee table




Responses

Posted by: Vuyiswamb on: 7/10/2014 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
you are talking about referential integrity


if you have a Table that has Department and a table that has Employees , you know that Employees will always belong to a department and in the Employee table there will be a Foreign key linked to the Department table.

So basically if you want to delete a Department you can check if there is a record in an Employee table that has a Department_ID(Foreign key) that is in Department table.

The same way is not to delete the department , just move the users to a certain department. in a database you must refrain from Deleting , just archive.


Thank you for posting at Dotnetfunda
[Administrator]

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Haider_Sigma on: 7/10/2014 [Member] Starter | Points: 25

Up
0
Down
need sql query
check if there is a record in an Employee table that has a Department_ID(Foreign key) that is in Department table.


Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/10/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Sample Code for checking the child tables before deleting data from parent table

DECLARE @DeptID INT = 120
IF NOT EXISTS (SELECT 1 FROM Employees where Department_ID = @DeptID)
DELETE FROM Departments WHERE Department_ID = @DeptID


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 7/11/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Check with below code:-
Declare @cnt Int = 0;
Select @cnt = count(*) from Employee where dept_id = your id;

if(@cnt > 0)
begin
delete from Employee where dept_id = your id;
end


Haider_Sigma, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response