Changing SQL CONSTRAINT Based On Other Fields

Question

I have an SQL table written in MSSQL:

create table [action]
(
    action_id  bigint identity not null, -- PK
    action_action char(1) not null,  -- 'C' Call, 'R' Raise, 'F' Fold, 'P' Post
    action_size  decimal(9,2) not null, -- zero if fold, > zero otherwise

    constraint pk_action primary key clustered (action_id),
    constraint chk_action_action check (action_action in('C','R','F','P'))
)

I want to put a constraint on the action_size column such that:

1) If action_action is 'F' then action_size must be 0.00 (or null if that is more doable) 2) If action_action is anything other than 'F' then action_size must be greater than zero (ie >= 0.01)

How do I express this? I tried:

constraint chk_action_size check (
 select action_action
  case 'F' action_size = 0.00
  else  action_size > 0.00
)

...to no avail.

I'm writing this in MSSQL 2005 but would like a solution that works with MySQL 5.1.34 also.

BTW if you would care to comment on my action_action column, feel free. There will either never be other valid values for action_action or, if there are, it will be exceedingly rare and there will only be ~1 other valid value.

Answer 1

What about using a trigger?

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Answer 2

ALTER TABLE action ADD CONSTRAINT chk_action_size CHECK (
    (action_action = 'F' AND action_size = 0.00)
    OR (action_action <> 'F' AND action_size > 0.00)
)

If you were using floats instead of decimals, write the zero check as:

ABS(action_size) > 0.01

Since floats may not be exactly zero, especially after some math.

Answer 3

create table [action]
(
    action_id           bigint identity not null,
    action_action       char(1) not null, 
    action_size         decimal(9,2) not null,

    constraint pk_action primary key clustered (action_id),
    constraint chk_action_action check (action_action in('C','R','F','P')),
    constraint chk_action_size check 
    (
         (action_action = 'F' AND action_size = 0.00) OR
         (action_action <> 'F' AND action_size > 0.00)
     )
)