March 10, 2017 at 8:29 am
I'm out of ideas, and I need help. Here's the set-up: I have a stored procedure that creates and executes dynamic update statements. The script updates each table that contains the MRN column. As you can see, I'm already using NOCHECK CONSTRAINT in the script. However, I am still getting an error when I run it.
begin tran
alter table dim.[PatientEpisode] nocheck constraint FK_PatientEpisode_MRN
update dim.[PatientEpisode]
set MRN = '111111111'
where MRN = '222222222'
alter table dim.[PatientEpisode] check constraint FK_PatientEpisode_MRN
commit TRAN
Msg 547, Level 16, State 0, Line 4
The UPDATE statement conflicted with the REFERENCE constraint "FK_PatientEpisode_MRN". The conflict occurred in database "TestV6", table "dim.PatientEpisode", column 'MRN'.
The statement has been terminated.
Can anyone explain why I would get this error after using the NOCHECK option?
March 10, 2017 at 8:38 am
My understanding, and I'm sure someone will correct me if I'm wrong, is that the issue is you set the table back to CHECK CONSTRAINT. That causes the tables to be checked for violations. SInce it happened within a transaction, the violation was spotted and rolled back. If you are going to violate the FK, you have to leave NOCHECK on.
-SQLBill
March 10, 2017 at 8:39 am
What are you hoping to achieve here? Even if the NOCHECK option did overlook the foreign key violations, you'd get an error anyway when your try to turn NOCHECK off. The foreign key constraint is there for a reason - don't try to bypass it.
John
March 10, 2017 at 8:56 am
Thank you for your replies. For clarification, sometimes patients get entered twice. After a decision is made as to which patient record should be the final one, the MRN is changed on all other tables (about 400) to update the MRN to the correct number. There are many other tables for insurance, lab results, etc. All of the other tables have a foreign key, and they are all updating as expected. Just this one table is causing the error.
I just tried moving the CHECK outside the COMMIT TRAN. The result was the same.
begin tran
alter table dim.[PatientEpisode] nocheck constraint FK_PatientEpisode_MRN
update dim.[PatientEpisode]
set MRN = '111111111'
where MRN = '222222222'
commit TRAN
alter table dim.[PatientEpisode] check constraint FK_PatientEpisode_MRN
March 10, 2017 at 9:04 am
Sector7G - Friday, March 10, 2017 8:56 AMThank you for your replies. For clarification, sometimes patients get entered twice. After a decision is made as to which patient record should be the final one, the MRN is changed on all other tables (about 400) to update the MRN to the correct number. There are many other tables for insurance, lab results, etc. All of the other tables have a foreign key, and they are all updating as expected. Just this one table is causing the error.I just tried moving the CHECK outside the COMMIT TRAN. The result was the same.
begin tran
alter table dim.[PatientEpisode] nocheck constraint FK_PatientEpisode_MRN
update dim.[PatientEpisode]
set MRN = '111111111'
where MRN = '222222222'
commit TRAN
alter table dim.[PatientEpisode] check constraint FK_PatientEpisode_MRN
Please post the CREATE TABLE script for dim.PatientEpisode, including the FK definitions.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 10, 2017 at 9:07 am
Then chances are, your new value still violates the FK constraint. Check the table referenced by the FK for the value you're looking to insert and see if it's there. It may be there for the other tables, but maybe just not this one?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 10, 2017 at 9:10 am
Sector7G - Friday, March 10, 2017 8:56 AMFor clarification, sometimes patients get entered twice.
Looks like you're missing a primary key or unique constraint in your patients table. I strongly recommend you fix that problem, rather than trying to work round it.
John
March 10, 2017 at 9:24 am
Phil Parkin - Friday, March 10, 2017 9:04 AMPlease post the CREATE TABLE script for dim.PatientEpisode, including the FK definitions.
Thanks - here is the create script:
CREATE TABLE [dim].[PatientEpisode](
[dimEpisodeID] [int] IDENTITY(1,1) NOT NULL,
[MRN] [varchar](15) NOT NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[EpisodeTypeID] [int] NOT NULL,
[EpisodeSubTypeID] [int] NOT NULL,
[PhaseID] [int] NOT NULL,
[StatusID] [int] NOT NULL,
[EpisodeCount] [int] NULL,
[StatusChangeCount] [int] NULL,
CONSTRAINT [PK_PatientEpisode] PRIMARY KEY CLUSTERED
(
[dimEpisodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dim].[PatientEpisode] WITH NOCHECK ADD CONSTRAINT [FK_PatientEpisode_MRN] FOREIGN KEY([MRN])
REFERENCES [dbo].[Person] ([MRN])
ON DELETE CASCADE
GO
ALTER TABLE [dim].[PatientEpisode] CHECK CONSTRAINT [FK_PatientEpisode_MRN]
GO
March 10, 2017 at 12:13 pm
When you do CHECK CONSTRAINT, it means what it says....it has to check the constraint against all values. So your entry violates the FK constraint and you need to check the values to figure out why. Once you know which value violates the FK and with which table(s), then you can correct the issue. But as long as you are having SQL Server check the values against the constraint, you need to make sure all the values meet the FK criteria.
-SQLBill
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply