SQL Server check constraint logic -

i've got table has such kind of structure:

create table #mine (     productid int     , countryid int     , applicationid int ); 

let's assume has data follows:

productid   countryid   applicationid 1           2           -1 1           3           -1 1           3            2 

i'd enforce such logic there's no other productid/countryid combination in entire table if exists applicationid = -1. in example 2nd , 3rd row wouldn't pass this.

i create custom function validate , make check constraint out of it. there perhaps more elegant way it?

i split task. first, assign unique constraint (this can table key):

create unique index ix_uq on mine(productid, countryid, applicationid) 

this trivial validations , improve trigger query.

second, check requires many records involved (no check constraint possible). task trigger:

create trigger trmine on mine insert,update if (exists(         select mark         (             select max(case when m.applicationid=-1 1 else 0 end)*(count(*)-1) mark             mine m             join inserted on m.productid=i.productid , m.countryid=i.countryid             group m.productid,m.countryid         ) q         mark != 0     )) throw 50000, 'validation error', 1 

when there 2 or more records (count(*)-1>0) , there record applicationid=-1, mark evaluates != 0. violation rule.