Check contraint for two columns

Question

I have a table where there are two columns: RowType (INT) and RefId (INT). The value of both columns are zero or more.

I need to check:

If RowType == 1 Then RefId cannot be = 0

What is the best way to do it ?

As I see I can check

CREATE TABLE (
  ...
  CONSTRAINT MyCheck CHECK (RowType + RefId <> 1)

to do it, but may be the is a more general way for such checkings ?

Useful link - https://stackoverflow.com/questions/3438066/check-constraint-on-multiple-columns — Abhishek, Feb 28 '18 at 12:46
How can I apply to my case ? I know how to use check constrain on the table level. My question about using `if-then` logic in the check constraint. — ceth, Feb 28 '18 at 12:48

score 5 · Accepted Answer · answered Feb 28 '18 at 12:49

5

You can use a check constraint:

alter table t add constraint chk_t_2cols
    check (rowtype <> 1 or refid <= 0);

Or, if you prefer:

alter table t add constraint chk_t_2cols
    check (not (rowtype = 1 and refid > 0) );

Note: There might be slight modifications if the columns can be NULL.

answered Feb 28 '18 at 12:49

Gordon Linoff

Hmm.. thanks. Didn't know that it is possible to boolean expressions in CHECK's – ceth Feb 28 '18 at 12:50

1 Answers1