At 02:53 AM 4/01/2005 +0000, you 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?

Yes. Don't do this sort of thing. Use a UNIQUE constraint instead. The
engine has had a lot more practice than we have at deciding the logic of
"unique".

ALTER TABLE ATABLE
ADD CONSTRAINT UQ_ID_SEQUENCE
UNIQUE (
STORECODE,
CTRLID,
STOCKCODE,
SERIALNO );

./heLen