Transact SQL – The UPDATE statement conflicted with the REFERENCE constraint “FK_”

Background

Tried to update a database record using a GUI Tool and was greeted with a bit of an opaque error message.

Since it is an Application that I am writing and I know what I am asking the tool to do, I launch a query tool and entered a similar query.

 

Query

Code


declare @serverName sysname
declare @serverNameNew sysname

set @serverName = 'currentServerName'
set @serverNameNew = 'NewServerName'

begin tran

	update tblLS
	set    tblLS.serverName = @serverNameNew
	from   [dbo].[listofServers] tblLS
	where  tblLS.[serverName] = @serverName 


rollback tran

Error Message


Msg 547, Level 16, State 0, Line 13
The UPDATE statement conflicted with the REFERENCE constraint "FK_logofDBMonitorSession". 
The conflict occurred in database "HRDB", table "dbo.logofDBMonitorSession", column 'serverName'.
The statement has been terminated.


TroubleShooting

I went back and looked at the Foreign Key Definition on the Referencing Table.
And, here is what the script looks like.

Current Foreign Key Code

Code

ALTER TABLE [dbo].[logofDBMonitorSession]  
WITH CHECK ADD  CONSTRAINT [FK_logofDBMonitorSession] 
FOREIGN KEY
(
	[serverName]
)
REFERENCES [dbo].[listofServers] 
(
	[serverName]
)
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[logofDBMonitorSession] 
CHECK CONSTRAINT [FK_logofDBMonitorSession]
GO




 

Redefine Foreign Key

Code


begin tran

	if exists
	(
		select *
		from   sys.foreign_keys tblSFK
		where  tblSFK.[parent_object_id] = object_id('[dbo].[logofDBMonitorSession]')
		and    tblSFK.[referenced_object_id] = object_id('[dbo].[listofServers]')
		and    tblSFK.[name] = 'FK_logofDBMonitorSession'
		and    tblSFK.update_referential_action_desc = 'NO_ACTION'
	)
	begin

		print 'On [dbo].[logofDBMonitorSession], Droping FK FK_logofDBMonitorSession'

		ALTER TABLE [dbo].[logofDBMonitorSession]  
			DROP CONSTRAINT [FK_logofDBMonitorSession] 

		print 'On [dbo].[logofDBMonitorSession], Adding FK FK_logofDBMonitorSession'

		ALTER TABLE [dbo].[logofDBMonitorSession]  
		WITH CHECK ADD  CONSTRAINT [FK_logofDBMonitorSession] 
		FOREIGN KEY
		(
			[serverName]
		)
		REFERENCES [dbo].[listofServers] 
		(
			[serverName]
		)
		ON DELETE CASCADE
		ON UPDATE CASCADE

		print 'On [dbo].[logofDBMonitorSession], Establishing FK FK_logofDBMonitorSession'
		ALTER TABLE [dbo].[logofDBMonitorSession] 
			CHECK CONSTRAINT [FK_logofDBMonitorSession]

	end

	declare @commit bit
	
	set @commit= 0

	while (@@TRANCOUNT > 0)
	begin

		if (@commit = 0)
		begin

			print 'rollback'
			
			rollback;
			
		end
		else
		begin

			print 'commit'

			commit;

		end

	end

go

 

Quick Explanation

  1. Drop Foreign Key
  2. Add the “On Update Cascade” Clause

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s