Geek Answers Handbook

Why does the error `Trying to set a column value other than NULL to NULL 'occur when changing a command sometimes?

I use instructions mergeto insert new data or delete data. I use temp tablelike a sourcetable like target.

the goal table has foreign key with another table ( Table A).

I sometimes encounter an error when executing ( when matched then deletestatement).

error:
    Attempting to set a non-NULL-able column value to NULL.

If I comment on this line, the request will execute perfectly.

Also, if I delete the request foreign keybetween requests Material tableand Table Aalso fine.

I applied this hotfix , but I already mentioned the problem.

SQL Server Version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)

**Target Tbale (Material)**                **Table A**

PatientIdRef (ForeignKey)             PatientId(Primary Key)
VisitNumberRef(Foreign Key)           VisitNumber(Primary Key)
Unit_Price                            Name
Unit_Price_Ins                        family
....
....                            

create tabl #Temp 
(
  patinetId [varchar](50) NOT NULL,
  [Visit_Number] [smallint] NULL,
  [Unit_Price] [float] NULL,
  [Unit_Price_Ins] [float] NULL,
  ......
  .....
)
merge materials as target
using(select patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins
from #Temp
) as source
on (source.patientid=target.patientid collate arabic_ci_as and source.visit_number=target.visit_number)
when matched then delete
when not matched then insert
(patinetIdref ,Visit_Numberref,Unit_Price,Unit_Price_Ins )
values(patinetId ,Visit_Number,Unit_Price,Unit_Price_Ins)
+4
2

, (FK), , , : : " NULL- NULL" MERGE SQL Server 2008, SQL Server 2008 R2 SQL Server 2012

SQL Server 2008 R2 SP1 10.50.2550

SQL Server 2008 R2 SP1 10.50.2822 ( 8).

: 8 SQL Server 2008 R2 1 (SP1)

, , , SQL Server 2008 R2 1 (SP1): 13 SQL Server 2008 R2 SP1

, SQL Server , , SELECT @@VERSION , - 10.50.2822 .

+1

:

  • , NON-NULLABLE NULL
  • .
  • MERGE when matched then delete

, , Materials, ON DELETE SET NULL. , - , Materials, EXEC sp_fkeys 'Materials'

0

More articles:


All Articles