Re: supersunday modeling question

On Feb 5, 4:51 am, "foothillbiker" <foothillbi..._at_gmail.com> wrote:
> All,
>
> Suppose I have a table like this:
>
> 15:14:55 system_at_ta64 SQL> desc rowshambow
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ROWSHAMBOWID NOT NULL NUMBER
> ROCKID NUMBER
> PAPERID NUMBER
> SCISSORSID NUMBER
>
> rockid/paperid/scissorsid are all foreign keys to rocktable/
> papertable/
> scissorstable, respectively.
>
> The biz requirements are that every record in the table must have
> exactly one (1) of these three fields populated.
>

As Bob indicated, you should consider normalizing your design. Meanwhile you could do something like:

CHECK ((case when ROCKID IS NOT NULL then 1 else 0 end +

              case when PAPERID IS NOT NULL then 1 else 0 end +
              case when SCISSORSID IS NOT NULL then 1 else 0 end ) =

/Lennart

[...] Received on Mon Feb 05 2007 - 14:59:48 CST