Can you not just create a Unique Index on these fields? You will
probably want to query based on these values and so it may be
advantageous to have it indexed anyway.

create unique index ix_example on MyTable
(STORECODE,CTRLID,STOCKCODE,SERIALNO);

Adam

--- In firebird-support@yahoogroups.com, "jobenza02" <jao@s...> wrote:
>
> 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