CODEWITHSUNDEEP
sqlsql-serverconstraints
tom.amourette
tom.amourette

Reputation: 35

SQL Check constraint on column referencing other columns

I want to limit a column that it can only have a value when another column has a value.

example: (this doesn't work)

create table testConstraint (
col1 int not null identity(1, 1) primary key,
col2 int,
col3 int check (col2 is not null),
col4 int)

This is not possible because he cannot reference another column. Error:

Column CHECK constraint for column 'col3' references another column, table 'testConstraint'.

Another try was: (also doesn't work)

create table testConstraint (
col1 int not null identity(1, 1) primary key,
col2 int,
col3 int,
col4 int)
GO

alter table testConstraint add constraint ck_columnNotNull check (case when col2 is null then col3 is null end)
GO

Anyone have an idea how this would be possible with a constraint?

Upvotes: 2

Views: 6909

Answers (3)

peter.petrov
peter.petrov

Reputation: 39477

You can write a trigger.

Also, you can try this

(1)

ALTER TABLE TestConstraint ADD CONSTRAINT
    CK_TestConstraint CHECK (NOT ( (col3 is not null) and (col2 is null) ))
GO

or this

(2)

ALTER TABLE TestConstraint ADD CONSTRAINT
CK_TestConstraint CHECK 
(
    ((col3 is not null) and (col2 is not null)) or 
    ((col3 is null) and (col2 is null))
)
GO

depending on what exactly you need.

I just tested it and it works OK, I think.

insert into 
TestConstraint
(col2, col3, col4)
values
(null, 1, 2)

-- ERROR

insert into 
TestConstraint
(col2, col3, col4)
values
(1, 1, 2)

-- OK

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

Only simple logic is required, plus it needs (as per your second attempt) to be a table check constraint, so you can't declare it inline with the declaration of col3:

create table testConstraint (
col1 int not null identity(1, 1) primary key,
col2 int,
col3 int,
col4 int)
GO

alter table testConstraint add constraint ck_columnNotNull check (
   col3 is null
or col2 is not null
)
GO

If col3 is null, then we don't care what the value of col2 is. Conversely, if it's not NULL, then we do want to enforce the col2 isn't null. That's what the two sides of the or effectively give us.

Upvotes: 0

bjnr
bjnr

Reputation: 3437

ALTER TABLE testConstraint
ADD CONSTRAINT ck_columnNotNull
CHECK ( 1 = CASE
                WHEN col2 IS NULL AND col3 IS NULL THEN 1 
                WHEN col2 IS NOT NULL AND col3 IS NOT NULL THEN 1 
            ELSE 0 
        END)

Upvotes: 0

Related Questions