Skip to content
Advertisement

Add check constraint related to other column

My table structure is as below

User  |Group   |IsActive   |GoSequence
--------------------------------------
1     |max     |1          |4
2     |jun     |0          |0
3     |cle     |1          |1
4     |trk     |1          |2
5     |wdr     |0          |6

User=int, Group=varchar(3), IsActive=bit, GoSequence=int

I want to add a constraint to this table where only if IsActive=1, GoSequence value can be change.

If IsActive=0, GoSequence stays 0.

ALTER TABLE [pbr_TeamPel] ADD CONSTRAINT [CHK_pbr_TeamPel_GoSeq]
    CHECK ~

/*If IsActive = 0 then GoSequence=0, else 
if IsActive = 1, GoSequence can be change to other int*/

Any help is appreciated. Thanks!

Advertisement

Answer

Probably the simplest way of expressing that is that either GoSequence must be 0 or IsActive must be 1.

So your constraint could look like this:

ALTER TABLE [pbr_TeamPel] ADD CONSTRAINT [CHK_pbr_TeamPel_GoSeq]
    CHECK(GoSequence=0 Or IsActive=1)
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement