sql server - Unique constraint keys not invoked when a field is NULL -

i have table named aqi, following column

only 1 unique value of date, pollutant_code, source_id , time able exist, defined unique constraint source_code_time_uq.

and upsert query like

insert aqi(source_id, source_type, pollutant_code, "date", "time", aqi_value) values (4, 1 ,'pm2.5','2018-05-28',8, 789) on conflict on constraint source_code_time_uq update set     aqi_value = 789 

the upsert method work when field available, when put null in time column (in attempt make row represent aqi data whole day), source_code_time_uq constraint doesn't invoked , still insert new row.

so how can add unique constraint check on field can null , need update upsert query ?

you can use filtered unique index:

create unique index unq_aqi_4 on (date, pollutant_code, source_id, time)     time not null; 

then, want guarantee 1 null row per day, so:

create unique index unq_aqi_4 on (date, pollutant_code, source_id)     time null; 

of course, remove unique constraint well.