Our DB vendor is now implementing a new FK. This new FK is not going in due to duplicates -
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PractitionerInsurance' and the index name 'UX_PractitionerInsurance_PractitionerID_CarrierID_TypeID_Policy_EffectiveDate'. The duplicate key value is (43246, 96146, 13261, K1510, Aug 9 1988 12:00AM).
Thinking I need to update the effectivedate by changing the date by one hour Thus saving the record and allowing the new Unique Index to be created without the error.
So I run this to get the practitionerrecid:
select * from [PractitionerInsurance] where PractitionerID = 43246
Then I manually update the effectivedate using the newly found pratitionerinsurancerecid.
Update [PractitionerInsurance] set EffectiveDate = '2021-12-01 01:00:00.000' where PractitionerInsuranceRecID = '675283' which just updates the effective date of the duplicated record.
Then when I go to apply the new unique index it errors for the next 'bad' record.
Been trying for hours to think of a way to automate this process as there allot of these.
Is there a way for me to keep trying at add the index and then based on whats returned update the effective date hour (only)?
- This topic was modified 5 months, 3 weeks ago by krypto69.