>>>>Hi,
>>>>
>>>>I have the following constraint set on the foreign key field of the child table:
>>>>
>>>>

>>>>ALTER TABLE [dbo].[child_tbl]  WITH CHECK ADD  CONSTRAINT [FK_child_tbl_employee] FOREIGN KEY([employee])
>>>>REFERENCES [dbo].[parent_tbl] ([employee])
>>>>

>>>>
>>>>Why do you think the following code in the SSMS gets error of constraint conflict?
>>>>
>>>>

>>>>use [MyDatabase]
>>>>go 
>>>>begin transaction
>>>>update parent_tbl set employee = 'TEST34' where employee = 'TEST1'
>>>>go
>>>>update child_tbl set employee = 'TEST34' where employee = 'TEST1'
>>>>go
>>>>commit 
>>>>

>>>>
>>>>TIA
>>>
>>>GO statement in SSMS ends the batch (it's a bacth separator). You started transaction but you never ended it in the first batch, so I assume it rollbacked by default. You need to commit it before GO, then the second statement will work. But also, for a single statement you don't need a transaction at all.
>>
>>Thank you for the explanation. Removing the GOs does not solve my problem. I just thought maybe someone in SQL Server forum will see a solution.
>
>As I said, in this particular case you need to commit the first statement in order to be able to run the second. Try
>

>begin transaction
>  
>   update Employee set EmpNo = 'Test10' where EmpNo = 'Test32'
>commit transaction   
>   update Salaries set EmpNo = 'Test10' where EmpNo = 'Test32'
>

Yes, this does work but it does not make sense to me. How come SQL Server allows the first update to work and change the value in the parent table and not throw an exception? In other words, even if you comment the second UDPATE, the first one does not fail. Then the Constraint does not work, does it?