sql server - The INSERT statement conflicted with the CHECK constraint.conflict occurred in database -

the solution above subject line exists in many links , have come across links , find no solution issue.

here query :

 insert patientpatternelements (patientpatternsid, aseventid, locationid, effectivedate, effectivedateend, workloadstartdate, workloadenddate)                 select distinct patientpatterns.id,                          @hl7event_id,                          @default_locationid,                         @hl7event_effective_date,                          @hl7event_effective_date,                          @hl7event_effective_date,                          @hl7event_effective_date                  patientpatternelements                 inner join patientpatterns                     on patientpatternelements.patientpatternsid = patientpatterns.id                 patientpatterns.patientencounterid = @patient_encounter_id                     , patientpatterns.visit = @patient_encounter_visit                  ---------------------                 -- error checking                 ----------------------                 set @syserror =  @@error                 if @syserror != 0                 begin                     set @ierror = 23063                     set @strerrormsg = 'error applying manual discharge event, error: ' + cast(@syserror varchar(256)) + ' encounter_id=' + cast(@patient_encounter_id varchar(64))                     goto error                  end 

it part of code of stored procedure.

workflow

it happening in 1 of version in client testing environment , client has 1 more version , flow works without issues. when trying run record manually throwing error in 1 of our log table , throws below message:

thread 4: applypatientpatternevents: recovering sql exception: insert statement conflicted check constraint "ck_patientpatternelements_location".  conflict occurred in database "db_name", table "dbo.patpatternelements". 

and this

rolling transaction. sphl7applypatientpatternevents: error applying manual discharge event, error: 547 encounter_id=20 

as said earlier happening in 1 version, stored procedure identical in both versions, making hard find out issue.

there nothing logged in eventviewer , tracing sql profiler not helping since sp sphl7applypatientpatternevents never show in profiler.

anyone can give idea has happened ?

this query of constraint.

alter table [dbo].[patientpatternelements]  check add  constraint [ck_patientpatternelements_location] check  (([locationid] null , [levelofcaregroupid] not null or [locationid] not null , [levelofcaregroupid] null)) go  alter table [dbo].[patientpatternelements] check constraint     [ck_patientpatternelements_location] go 

manjuboyz,

the following points may understand happening in code.

1. as have mentioned "there 2 environments. , insert statement working fine in 1 environment , not in other". based on statement, can suggest check if same constraint present in both environments.

2. check constraint here checking whether values getting inserted have specified value. if fails constraint fail error mentioned. please exception handling try, catch placing insert statement under try. if there error in catch block can capture error, , values of [locationid] , [levelofcaregroupid] getting inserted.

3. alter table [dbo].[patientpatternelements] check add constraint [ck_patientpatternelements_location] check (([locationid] null , [levelofcaregroupid] not null or [locationid] not null , [levelofcaregroupid] null)) go

based on above check constraint have provided can understand that, insert statement work when "any 1 of paramters" i.e. [locationid] or [levelofcaregroupid] null. not both.

i.e. if [locationid] null, , [levelofcaregroupid] null. insert fail

similarly, if [locationid] not null, , [levelofcaregroupid] not null. insert fail