TABLE USER
IDNO PK
USERNAME
PASSWORD

TABLE GROUP
IDNO PK
NAME


TABLE GROUPMEMBER
IDNO PK
GROUPIDNO
MEMBER

Is it posible the MEMBER in GROUPMEMBER to reference two different
Foreign keys: IDNO in USER and IDNO in GROUP? Basicaly the MEMBER of
the GROUP can be a USER or another GROUP. How to implement this?

Thanks
Dimitris

In article <1708e070.0310091356.60a7770d@posting.google.com >,
ilaridis@hotmail.com says...
> TABLE USER
> IDNO PK
> USERNAME
> PASSWORD
>
> TABLE GROUP
> IDNO PK
> NAME
>
>
> TABLE GROUPMEMBER
> IDNO PK
> GROUPIDNO
> MEMBER
>
> Is it posible the MEMBER in GROUPMEMBER to reference two different
> Foreign keys: IDNO in USER and IDNO in GROUP? Basicaly the MEMBER of
> the GROUP can be a USER or another GROUP. How to implement this?
>
> Thanks
> Dimitris
>


You need to use triggers, you can't use foreign keys for this
situation.

"Dimitris" <ilaridis@hotmail.com> wrote in message
news:1708e070.0310091356.60a7770d@posting.google.c om...
> TABLE USER
> IDNO PK
> USERNAME
> PASSWORD
>
> TABLE GROUP
> IDNO PK
> NAME
>
>
> TABLE GROUPMEMBER
> IDNO PK
> GROUPIDNO
> MEMBER
>
> Is it posible the MEMBER in GROUPMEMBER to reference two different
> Foreign keys: IDNO in USER and IDNO in GROUP? Basicaly the MEMBER of
> the GROUP can be a USER or another GROUP. How to implement this?
>
> Thanks
> Dimitris
I don't know exactly what you are doing, but having 3 tables with the same
PK (IDNO) is questionable from a logical design point of view, never mind
trying to implement Referential Integrity.

No, such RI would be semantic nonsense.

Your data model is the problem. What you want (as far as I can make out) is
groups that can contains groups or users; it is this semantic that needs to
be modelled. You do this by creating a "super-table" that consists of group
members, then you create two "sub-tables" for the users and groups that are
the actual group member objects. Your RI is with the super-table.

Review CREATE TABLE - in particular, the OF, UNDER and HIERARCHY options -
in the SQL Reference for further information.

"Dimitris" <ilaridis@hotmail.com> wrote in message
news:1708e070.0310091356.60a7770d@posting.google.c om...
> TABLE USER
> IDNO PK
> USERNAME
> PASSWORD
>
> TABLE GROUP
> IDNO PK
> NAME
>
>
> TABLE GROUPMEMBER
> IDNO PK
> GROUPIDNO
> MEMBER
>
> Is it posible the MEMBER in GROUPMEMBER to reference two different
> Foreign keys: IDNO in USER and IDNO in GROUP? Basicaly the MEMBER of
> the GROUP can be a USER or another GROUP. How to implement this?
>
> Thanks
> Dimitris

Gert van der Kooij <gert@invalid.nl> writes:

>In article <1708e070.0310091356.60a7770d@posting.google.com >,
>ilaridis@hotmail.com says...
>> TABLE USER
>> IDNO PK
>> USERNAME
>> PASSWORD
>>
>> TABLE GROUP
>> IDNO PK
>> NAME
>>
>>
>> TABLE GROUPMEMBER
>> IDNO PK
>> GROUPIDNO
>> MEMBER
>>
>> Is it posible the MEMBER in GROUPMEMBER to reference two different
>> Foreign keys: IDNO in USER and IDNO in GROUP? Basicaly the MEMBER of
>> the GROUP can be a USER or another GROUP. How to implement this?
>>
>> Thanks
>> Dimitris
>>


>You need to use triggers, you can't use foreign keys for this
>situation.

I would recommend a CHECK constraint on GROUPMEMBER over triggers as
it is easier to get it right and see it is correct when you have a
declarative constraint. Triggers are operational procedures and you
have to consider very carefully what happens on inserts, deletes, updates
and verify that the trigger executions maintain the proper constraint
in all scenarios. Moreover, triggers can have crosstalk with each other
so as the number of triggers grows over time, you have to consider all
the interactions to verify correctness. With no declarative semantics
for triggers, this can be difficult.

You just have to check that

MEMBER IN ((SELECT IDNO FROM GROUP) UNION (SELECT IDNO FROM USER))

which could also be expressed as

MEMBER IN (SELECT IDNO FROM GROUP) OR MEMBER IN ((SELECT IDNO FROM USER)

Cheers,

Joseph