I only skimmed through the answers, so I might have missed a similar answer. The idea is to use a generated column that is either the p.k or a constant that does not exist as a value for the p.k.

create table foo 
(  bar int not null primary key
,  chk char(1) check (chk in('Y', 'N'))
,  some_name generated always as ( case when chk = 'N' 
                                        then bar 
                                        else -1 
                                   end )
, unique (somename)
);

AFAIK this is valid in SQL2003 (since you where looking for an agnostic solution). DB2 allows it, not sure how many other vendors that accepts it.