Re: supersunday modeling question

On Feb 5, 12:22 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.
>
> Questions:
> - any clever ideas on how to enforce the rule about exactly one (1) of
> the fields having data? I assume a trigger is the only option, and
> it's not a thing of beauty
>

What about this:

ALTER TABLE RPS_TABLE ADD (
  CONSTRAINT RPS_TABLE_C1 CHECK ((ROCKID IS NOT NULL AND PAPERID IS NULL AND SCISSORSID IS NULL)
                                                              OR (ROCKID IS NULL AND PAPERID IS NOT NULL AND SCISSORSID IS NULL)
                                                         OR (ROCKID IS NULL AND PAPERID IS NULL AND SCISSORSID IS NOT NULL))
);

It's not a thing of beauty either, but imho it works.

cheers,
Martin Received on Mon Feb 05 2007 - 02:12:34 CST