Professional Web Applications Themes

One column with two Foreign Keys ??? - IBM DB2

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...

  1. #1

    Default One column with two Foreign Keys ???

    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
    Dimitris Guest

  2. #2

    Default Re: One column with two Foreign Keys ???

    In article <google.com>,
    com says... 


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

  3. #3

    Default Re: One column with two Foreign Keys ???

    "Dimitris" <com> wrote in message
    news:google.com... 
    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.


    Mark Guest

  4. #4

    Default Re: One column with two Foreign Keys ???

    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" <com> wrote in message
    news:google.com... 


    Mark Guest

  5. #5

    Default Re: One column with two Foreign Keys ???

    Gert van der Kooij <nl> writes:
     [/ref]

     

    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
    Joseph,,, Guest

Similar Threads

  1. Foreign keys tut
    By frizzle in forum MySQL
    Replies: 2
    Last Post: July 18th, 05:45 PM
  2. foreign keys
    By Sybrand Bakker in forum Microsoft Access
    Replies: 2
    Last Post: July 25th, 10:04 PM
  3. foreign keys - updated
    By Josh in forum Microsoft Access
    Replies: 2
    Last Post: July 24th, 04:01 PM
  4. DMO + Foreign Keys
    By oj in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 05:58 AM
  5. SQL DMO + Foreign Keys
    By Steven Campbell in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 4th, 09:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139