DELETE statement conflicted with the REFERENCE constraint

 -- Create Department table with necessary constraints

 CREATE TABLE [dbo].[Department](

    [DepartmentID] [int] NOT NULL IDENTITY(0,1),

    [DepartmentName] [varchar](50) NOT NULL,

    [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)

    CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

    (

            [DepartmentID] ASC

    ) ON [PRIMARY]

    ) ON [PRIMARY]

 -- Create Employee table with necessary constraints

 CREATE TABLE [dbo].[Employee](

    [EmployeeID] int NOT NULL IDENTITY(1,1),

    [DepartmentID] int NOT NULL CONSTRAINT FK_Employee_DepartmentID

                              REFERENCES [Department](DepartmentID),

    [EmployeeName] varchar(50) NOT NULL,

    [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender

                     CHECK ([Gender] in('M','F','O')),

    [BirthDate] date NOT NULL,

    [Salary] decimal(10,2) NULL,

    [Detail] varchar(max) NULL,

    [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),

    CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),

    CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

      (

          [EmployeeID] ASC

      ) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- Insert into Department table

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('(None)',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Computer Science',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Mathematics',1)

GO

INSERT INTO [dbo].[Department]

           ([DepartmentName]

           ,[IsActive])

VALUES ('Physics',1)

-- Insert into Employee table

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (1

           ,'Govindaraj Kanniappan'

           ,'M'

           ,'01/01/1970'

           ,10000.00

           ,'Govindaraj Kanniappan M.C.A, Mphil (CS)'

           ,1)

GO

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (2

           ,'Anusha Karayyan'

           ,'M'

           ,'01/01/1975'

           ,90000.00

           ,'Anusha MA'

           ,1)

INSERT INTO [dbo].[Employee]

           ([DepartmentID]

           ,[EmployeeName]

           ,[Gender]

           ,[BirthDate]

           ,[Salary]

           ,[Detail]

           ,[IsActive])

VALUES

           (1

           ,'Krishna Rajan'

           ,'M'

           ,'01/01/1973'

           ,90500.00

           ,'Krishna Rajan MCA'

           ,1)

GO

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
1	Computer Science
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
1	1	Govindaraj Kanniappan
2	2	Anusha Karayyan
3	1	Krishna Rajan

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

Error:

Msg 547, Level 16, State 0, Line 62

The DELETE statement conflicted with the REFERENCE constraint "FK_Employee_DepartmentID". The conflict occurred in database "TestDB", table "dbo.Employee", column 'DepartmentID'.

The statement has been terminated.

SOLUTION: 1

First delete the child table (Employee) rows that point to the row(s) you are trying to delete, then delete the row.

-- Delete from Employee table

DELETE [dbo].[Employee] WHERE DepartmentID = 1

(2 row(s) affected)

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

(1 row(s) affected)

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
2	2	Anusha Karayyan

SOLUTION: 2

You can modify the FK to add with “ON DELETE CASCADE”

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Employee_DepartmentID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [FK_Employee_DepartmentID]

GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_DepartmentID] FOREIGN KEY([DepartmentID])

REFERENCES [dbo].[Department] ([DepartmentID]) ON DELETE CASCADE

GO

-- Delete from Department table

DELETE [dbo].[Department] WHERE DepartmentID = 1

(1 row(s) affected)

-- Select from Department

SELECT DepartmentID, DepartmentName FROM Department WITH (NOLOCK)

DepartmentID	DepartmentName
0	(None)
2	Mathematics
3	Physics

-- Select from Employee table

SELECT EmployeeID, DepartmentID, EmployeeName FROM Employee WITH (NOLOCK)

EmployeeID	DepartmentID	EmployeeName
2	2	Anusha Karayyan