Re: constraint question on uniqueness

You may use following thing, if you wanna use only 2 values, and can use nulls instead of 'pending' value, you can define following scheme:

create yourtable (
...somecols...
  col1 char,
...somecols...
);

create unique index yourtable$UI$COL1 on mytable (col1);

alter table yourtable add (constraint yourtable_col1$CK check (col1='A'));

"ayrobins" <anthony_at_lumos.com> wrote in message news:ldNO7.93$eM5.6307_at_dca1-nnrp2.news.digex.net...
> Is there any way to create a constraint (probably a CHECK constraint) that
> would inforce uniqueness for
> one type of value for that column.
>
> e.g.
>
> row1, col1 = "active"
> row2, col1 = "pending"
> row3, col1 = "pending"
>
> Thus the value "active" can only be specified for one row at a time.
> "pending" will be specified for all other
> rows.
>
> thanx in advance.
>
>
Received on Mon Dec 03 2001 - 14:20:51 CST