Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013
    Posts
    1

    Unanswered: Problems creating check Constraint

    I need to make a constraint but I using 3 dates using this logic

    chk_StartDateRequestDate
    (
    Requestdate <= Startdate
    )

    But in the database is already a constraint that use this use this logic

    chk_FinishDateStartDate
    (
    StartDate <= FinishDate
    FinishDate IS NULL
    )

    so I need to create a new contraint or just adapt the one that already exist but when I create a new one Throws me this:

    The UPDATE statement conflicted with the CHECK constraint "chk_FinishDateStartDate". The conflict occurred in database "bd", table "dbo.test".

    And when i change the constraint that already exsit with from this:

    ALTER TABLE [dbo].[WorkOrder] WITH CHECK ADD CONSTRAINT [chk_FinishDateStartDate] CHECK (([FinishDate]>=[StartDate] OR [FinishDate] IS NULL))
    GO
    ALTER TABLE [dbo].[WorkOrder] CHECK CONSTRAINT [chk_FinishDateStartDate]

    To this:

    ALTER TABLE [dbo].[WorkOrder] WITH CHECK ADD CONSTRAINT [chk_FinishDateStartDate] CHECK (([FinishDate]>=[StartDate] OR [FinishDate] IS NULL))

    ALTER TABLE [dbo].[WorkOrder] CHECK CONSTRAINT [chk_FinishDateStartDate]

    Doesn't Work at all because i can create or update like always

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I need to make a constraint but I using 3 dates using this logic <<

    CONSTRAINT Chk_Start_Date_Request_Date
    CHECK (request_date <= start_date)

    >> But in the database is already a constraint that use this use this logic <<

    CONSTRAINT Chk_Finish_Date_Start_Date
    CHECK (start_date <= finish_date
    OR finish_date IS NULL)

    You do not seem to know that DDL and DML use two different Three valued logic rules. DDL gets the benefit of the doubt when the search condition is UNKNOWN. All you need is

    CONSTRAINT Chk_Finish_Date_Start_Date
    CHECK (start_date <= finish_date)

    I am guessing at the DDL you failed to post.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •