UPDATE statement conflicted (Msg 547) & Output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE statement conflicted (Msg 547) & Output

Author

Topic

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-23 : 21:32:44

I have the following code in AdventureWorks2012:

USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,
Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

I get the error message:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Employee_VacationHours". The conflict occurred in database "AdventureWorks2012", table "HumanResources.Employee", column 'VacationHours'.
The statement has been terminated.

1. What does this error mean?
2. In the OUTPUT statement, what is the purpose/significance of Deleted.BusinessEntityID? The column BusinessEntityID doesn't even appear in the code.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-23 : 23:52:03

1. There is a Check Constraint on the column VacationHours. You can right click it and script as create to view the definition.

2. It is returning the Primary Key of the row changed, and the before and after values.

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-25 : 21:16:12

quote:
Originally posted by russell

1. There is a Check Constraint on the column VacationHours. You can right click it and script as create to view the definition.

2. It is returning the Primary Key of the row changed, and the before and after values.

Here is the constraint definition:

USE [AdventureWorks2012]
GO

ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Default constraint value of 0' , @level0type=N'SCHEMA',@level0name=N'HumanResources', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'CONSTRAINT',@level2name=N'DF_Employee_VacationHours'
GO

1. As far as I know, constraints are used to restrict the values entered in a column. I don't understand how my code is violating any constraint. I'm not even sure what the constraint is.

2. Why does the OUTPUT statement need to return the Primary key? The last time I was able to run this, I tried it w/o the Primary key. If I remember correct, I got a different result compared to when leaving the Primary key in the code. So I don't understand the significance of including the Primary key column when the Primary key isn't even in the SET statement definition.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-26 : 00:16:33

quote:
The UPDATE statement conflicted with the CHECK constraint "CK_Employee_VacationHours"

quote:
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours]

Wrong constraint.

And I don't *know* it's the primary key, just guessing. There has to be a way to identify the row that changed.

Rock_query
Yak Posting Veteran

55 Posts

Posted - 2013-05-27 : 21:00:28

[quote]Originally posted by Rock_query

I have the following code in AdventureWorks2012:

USE AdventureWorks2012;
GO
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40
ELSE (VacationHours + 20.00)
END
)
OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue,
Inserted.VacationHours AS AfterValue
WHERE SalariedFlag = 0;

I get the error message:

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Employee_VacationHours". The conflict occurred in database "AdventureWorks2012", table "HumanResources.Employee", column 'VacationHours'.
The statement has been terminated.

I still don't understand what the source of the conflict is. Also, what is the constraint definition specifically?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-05-27 : 22:04:09

i think u hit this constraint...

ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_VacationHours] CHECK (([VacationHours]>=(-40) AND [VacationHours]<=(240)))
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources