SQL Server Performance

Msg 547, Level 16, State 0, Line 1 (Check Constraint Conflict)

Discussion in 'ALL SQL SERVER QUESTIONS' started by Rock_query, May 27, 2013.

  1. Rock_query New Member

    USEAdventureWorks2012;

    GO

    UPDATEHumanResources.Employee

    SETVacationHours=

    (CASE

    WHEN ((VacationHours- 10.00)< 0)THENVacationHours+ 40

    ELSE (VacationHours+ 20.00)

    END

    )

    OUTPUTDeleted.BusinessEntityID,Deleted.VacationHoursASBeforeValue,

    Inserted.VacationHoursASAfterValue

    WHERESalariedFlag= 0;

    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 get this 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 is the constraint definition?

    2. What is it in this code that is causing a constraint conflict?
  2. FrankKalis Moderator

    Welcome to the forums!

    I don't have AdventureWorks installed, so I can't tell you what the definition for that constraint looks like and why this statement causes the violation. But you can examine the definition yourself by running something like

    Code:
    SELECT
        CC.name,
        CC.definition
    FROM
        sys.check_constraints CC
    WHERE
        CC.name = 'CK_Employee_VacationHours'    
  3. davidfarr Member

    I have the AdventureWorks database on one of my servers.
    The output of Frank's query;

    Name
    CK_Employee_VacationHours
    Definition
    ([VacationHours]>=(-40) AND [VacationHours]<=(240))

    I would therefore say that this is your constraint violation;
    ELSE (VacationHours+ 20.00)
    Each time your query executes; it increments the VacationHours by 20 until it exceeds 240 and violates the constraint on the record(s).
  4. Rock_query New Member

    Just to be clear:

    1. What is 'sys' (table, schema, column, etc.)?
    2. What is 'check_constraints' (table, schema, column, etc.)?
    3. Which database is 'sys' located in?
    4. Which database is 'check_constraints' located in?
  5. FrankKalis Moderator

  6. Luis Martin Moderator

Share This Page