I have a check constraint on a table w/c I use to enforce uniqueness
on 4 columns. I tried:

(SERIALNO is null) or
(SERIALNO not in (select I.SERIALNO
from INVTRAN I
where I.STORECODE = INVTRAN.STORECODE
and I.CTRLID = INVTRAN.CTRLID
and I.STOCKCODE = INVTRAN.STOCKCODE)
)

STORECODE + CTRLID + STOCKCODE + SERIALNO must be unique.

If I have for my 1st entry:
'ST01' + '724' + '111' + '123ABC'

then for my 2nd entry:
'ST01' + '724' + '111' + '123XYZ'

then this is allowed, eveything works fine.
But when I do for my 2nd entry:
'ST01' + '724' + '222' + '123ABC'

then the check constraint is violated, w/c should not be the case
since it's a different STOCKCODE, even if it's the same SERIALNO
with the 1st entry.

Any tips?

TIA