Thanks, Helen, Adam, but as in my check constraint, SERIALNO can be
null, so I can't define a unique constraint on the 4 columns since
SERIALNO does not always have a value. In cases when SERIALNO does
have a value, I'd like to enforce the uniqueness on the 4 columns.
Any other way to do this if it can't be done thru a check constraint?

=


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> 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