If two records have identical values in all the other fields, and
serialno is null in both records, are they unique? The answer is
undefined, although I suspect in your model you would classify them
as identical. I would be checking out your database design to see if
it can be normalised at all. Your only other option would be to have
a before insert and before update trigger that threw an exception if
it found a duplicate. If you were going to implement anything like
that (messy), you had better make sure you have a good enough index
to minimise the work you are adding to it.

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