Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: multiple parents for one foreign key

    Hi,

    I am trying to figure out if this is possible in Oracle or Mysql

    Lets say I have 3 tables such that C could have either A or B as its parent.

    A
    { id, name}

    B
    {id, name}

    C
    {other_id, comment}

    Now other_id could be either A.id or B.id. What I want to be able to do is to define a foreign key constraint of the type:

    CONSTRAINT FK_C FOREIGN KEY (other_id) REFERENCES A(id) ON DELETE CASCADE,
    CONSTRAINT FK_C FOREIGN KEY (other_id) REFERENCES B(id) ON DELETE CASCADE

    such that deleting A.id automatically deletes C.other_id where A.id = C.otherid and same for B.id

    Ofcourse i am not able to do this. Is there any way that this can be done in Oracle and Mysql?

    Thanks a lot,
    Priyanka

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    You should post your question on the forum of Oracle or Mysql.

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    If your design is appropiate then,

    Table_Super(id)
    TableA(id) foreign key(id) references Table_Super(id);
    TableB(id) foreign key (id) references Table_Super(id);
    TableC(id) foreign key (id) references Table_Super(id);
    Last edited by r123456; 01-30-04 at 22:49.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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