Using 9.0.2.3xxx,

Is it possible to build in a unique constraint where a combination of 3
fields must be unique unless a 4th field is equal to zero, in which case
any number of duplicates would be allowed? Or would this need to be
handled with a trigger or other procedural operation?

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

That would seem to be one definition for a non-unique index criteria.
That would not be a candidate for any kind of table constraint.

You could add an artificial table (to hold all combinations of the values
for the 3 columns as it's primary key) and let constraining 3 columns be
a fkey to that. You would still need to add a before "insert"/"update of"
trigger on your existing table to pick up the zero'ed 4th column value
and do the necessary upsert (an INSERT ON EXISTING UPDATE)
that way.

Just a thought. I like this solution for possible performance reasons
as well as how it views in Sybase Central [somewhat self documenting]
and possible options for enhancing it for other bus. logic [such as cascades
etc].

Otherwise a standard trigger might be sufficient.

"David Kerber" <ns_dkerber (AT) ns_WarrenRogersAssociates (DOT) com> wrote

In article <MPG.1eb80534615698169897f3 (AT) forums (DOT) sybase.com>, ns_dkerber (AT) ns_WarrenRogersAssociates (DOT) com says...
This sounds that the table is not normalized properly.

Anyway, I think you need a trigger or a check constraint. Uniqueness as a colum property is done via an index and does not allow for conditioanls.

Martin