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

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

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

--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

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