Advertisement
elena1234

Disable or Re-create all constraints ( T-SQL )

Jan 10th, 2022 (edited)
3,865
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 0.97 KB | None | 0 0
  1. CREATE PROCEDURE usp_DeleteEmployeesFromDepartment (@departmentId INT)
  2. AS
  3. ALTER TABLE [EmployeesProjects] NOCHECK CONSTRAINT ALL -- disable all constraints in the tables
  4. ALTER TABLE [Employees] NOCHECK CONSTRAINT ALL
  5. ALTER TABLE [Projects] NOCHECK CONSTRAINT ALL
  6. ALTER TABLE [Departments] NOCHECK CONSTRAINT ALL
  7. ALTER TABLE [Addresses] NOCHECK CONSTRAINT ALL
  8. ALTER TABLE [Towns] NOCHECK CONSTRAINT ALL
  9.  
  10. DELETE FROM [dbo].[Employees]
  11. WHERE [dbo].[Employees].DepartmentID = @departmentId;
  12.  
  13. DELETE FROM [dbo].[Departments]
  14. WHERE [dbo].[Departments].DepartmentID = @departmentId;
  15.  
  16. ALTER TABLE [EmployeesProjects] CHECK CONSTRAINT ALL -- re-create all constraints in the tables
  17. ALTER TABLE [Employees] CHECK CONSTRAINT ALL
  18. ALTER TABLE [Projects] CHECK CONSTRAINT ALL
  19. ALTER TABLE [Departments] CHECK CONSTRAINT ALL
  20. ALTER TABLE [Addresses] CHECK CONSTRAINT ALL
  21. ALTER TABLE [Towns] CHECK CONSTRAINT ALL
  22.  
  23. SELECT COUNT(EmployeeID)
  24. FROM Employees
  25. WHERE DepartmentID = @departmentId;
  26.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement