SQL Server - Set column value based on other column

Question

I have 3 columns, Part1 , Part2 and OnePiece.

Part1 can be assigned to [1,2,3,4,5].

Part2 can be assigned to [1,2,3,4].

OnePiece it is of type Bit.

Both Part1 and Part2 can't have the same values and also if Part1 is 5 then Part2 can't have a value.

What I am trying to do is to set OnePiece to 1 and make sure Part2 don't accept any value when Part1 = 5.

How can I do that ?

---

I will clarify more my request.

The column OnePiece is a flag and is calculated automatically and is set to 1 if Part1 = 5, as for Part2 it can't be set to any value if Part1 = 5.

The reason is when Part1 = 5 it means that it is a one-part-product so there is no Part2 and must not allow any other value.

So that would make it two things I guess; a trigger and a check constraint. I hope that I have offered more details.

Answer 1

Here's a check constraint to forbid values in Part2 when OnePiece is 1 and Part5 is 5:

alter table YourTable add constraint CHK_YourTable
    check (OnePiece <> 1 or Part1 <> 5 or Part2 is null)

Answer 2

You can add a check constraint to assure this:

ALTER TABLE my_table
ADD CONSTRAINT my_table_chk
CHECK (part1 <> part2 -- "Part1 and Part2 can't have the same values"
       AND 
       (part1 <> 5 OR part2 IS NUILL) -- "if Part1 is 5 then Part2 can't have a value"
      );