Consider a simple table..

create table dbo.car(    car_guid UNIQUEIDENTIFIER default(newid()) 
                      ,  car_type varchar(20) not null
                      ,  wind_protector varchar(20) not null
)

insert into dbo.car(car_type, wind_protector) VALUES('HARD_TOP', 'NA')
insert into dbo.car(car_type, wind_protector) VALUES('CONVERTIBLE', 'FLAPBLAST_3')
insert into dbo.car(car_type, wind_protector) values('CONVERTIBLE', 'FLAPBLAST_2')

I'm trying to craft a check constraint that says if car_type is "CONVERTIBLE" then wind_protector can be "FLAPBLAST_2" or "FLAPBLAST_3". Otherwise the value of wind_protector is "NA". The column can not be null.

I have the basic check constraint written..

([wind_protector]='FLAPBLAST_3' OR [wind_protector]='FLAPBLAST_3')

I'm stuck on writing the check constraint across two columns and using and or logic.

Is it possible to do what I'm looking to accomplish?

Thanks,

CodePudding user response：

I think you're after the following constraint:

alter table car 
add constraint chk1 
check (
  ( car_type='CONVERTIBLE' and wind_protector in ('FLAPBLAST_2','FLAPBLAST_3')) 
    or wind_protector='NA' 
);

CodePudding user response：

You could do this with a simple multi-column constraint with AND OR logic

CHECK (
    car_type = 'CONVERTIBLE' AND wind_protector IN ('FLAPBLAST_2', 'FLAPBLAST_3')
    OR
    car_type = 'CONVERTIBLE' AND wind_protector = 'NA'
)

But you don't want a check constraint here. wind_protector is not a property of Car, it is a property of CarType. And it can have multiple wind_protector.

So you need to normalize your schema into proper Third Normal Form. You need a few more tables: CarType which contains each car type. Then WindProtector table contains possible options for wind protectors. And finally a table which joins them and defines which combinations are possible:

create table dbo.CarType (
    car_type varchar(20) not null primary key
);

insert dbo.CarType (car_type) VALUES
('HARD_TOP'),
('CONVERTIBLE');

create table dbo.WindProtector (
     wind_protector varchar(20) not null primary key
);

insert dbo.WindProtector (wind_protector) VALUES
('NA'),
('FLAPBLAST_2'),
('FLAPBLAST_3');

create table dbo.CarOptions (
     options_id int not null primary key  -- surrogate key
  ,  car_type varchar(20) not null references CarType (car_type)
  ,  wind_protector varchar(20) not null references WindProtector (wind_protector)
  ,  unique (car_type, wind_protector)
);

insert into dbo.CarOptions (options_id, car_type, wind_protector) VALUES
(1, 'HARD_TOP', 'NA'),
(2, 'CONVERTIBLE', 'FLAPBLAST_3'),
(3, 'CONVERTIBLE', 'FLAPBLAST_2');

create table dbo.Car (
    car_guid UNIQUEIDENTIFIER default(newid()) 
 ,  option_id int not null references CarOptions (options_id)
);

insert dbo.Car (option_id) VALUES
(1),
(2),
(3);

db<>fiddle

You may want to merge Car and CarOptions into one table, depending on requirements.

I would also recommend using NULL instead of 'NA'.