Post reply

Check constraint fail while updating row

  • June 23, 2014 at 12:57 am

    #287850

    Hi,

    i apply constraint to check two columns values.below is my table structure

    priority1 priority2 priority3 priority_hashvalue

    my check constraint is

    [priority_hashvalue]=hashbytes('md5',([priority1]+[priority2]+[priority3]))

    it's working fine while inserting new record into table.mismatched while updating record.

    priority_hashvalue generate using priority1,priority2,priority3 values.

    record updated successfully when pass old values as it is.

    thanks in advance.

    --chalam

  • June 23, 2014 at 1:33 am

    #1723907

    chalam87 (6/23/2014)

    Hi,

    i apply constraint to check two columns values.below is my table structure

    priority1 priority2 priority3 priority_hashvalue

    my check constraint is

    [priority_hashvalue]=hashbytes('md5',([priority1]+[priority2]+[priority3]))

    it's working fine while inserting new record into table.mismatched while updating record.

    priority_hashvalue generate using priority1,priority2,priority3 values.

    record updated successfully when pass old values as it is.

    thanks in advance.

    Priority_hashvalue is a calculated column?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 23, 2014 at 2:58 am

    #1723937

    Priority_hashvalue genereate like below

    insert into testtable(priroty1,priroty2,priroty3,Priority_hashvalue) values(@priroty1,@priroty2,@priroty3,hashbytes('md5',(@priroty1+@priroty2+priroty3)))

    --chalam

  • June 23, 2014 at 4:06 am

    #1723981

    chalam87 (6/23/2014)

    Priority_hashvalue genereate like below

    insert into testtable(priroty1,priroty2,priroty3,Priority_hashvalue) values(@priroty1,@priroty2,@priroty3,hashbytes('md5',(@priroty1+@priroty2+priroty3)))

    So it is hardcoded into the table.

    Ify you change the data in one of the priority columns, the checksum becomes different than the one stored in priority_hashvalue, so it is expected that the constraint will fail.

    I would either make priority_hashvalue a calculated column, or update priority_hashvalue as well when you update another column.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 23, 2014 at 4:33 am

    #1723999

    hi,

    updated priroty_hashvalue column also like below but it's constraint violated

    update testtable set priroty1=@priroty1,

    priroty2=@priroty2,

    priroty3=@priroty3,

    priroty_hashvalue=hashbytes('md5',@priroty1+@priroty2+@priroty3) where clause.

    --chalam

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply