Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: Foreign Key Referencing more than 1 table?

    I have an address table, and employee and customer tables. I am thinking of adding a faddress_owner field to the address table and use it as a foreign key referencing both the customer_no and the employee_no number. I have just done that on mysql, but I do not feel that confident about it - is this acceptable? what are the pros and cons? and how else can I handle it?
    Best Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see the thread Multiple Possible Parents

    or see this article: Supertypes and Subtypes

    your supertype is "entities which have addresses"

    you have two subtypes: customer and employee

    each of these subtype tables should have the supertype id as its primary key, as well as being a foreign key to the supertype table

    then your address table can have a foreign key to the supertype table too
    Last edited by r937; 02-16-04 at 15:53.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    6
    Thanks for the reply...
    Maybe I am a little slow - the tables look like this:

    create table customer (cust_No int not null, blah, blah, primary key(cust_no));

    create table employee (emp_No int not null, blah, blah, primary key(emp_No));

    create table address(addr_No int not null, addr_Owner int not null, blah, blah, primary key(addr_No), index(addr_Owner), Foreign Key(addr_Owner) references customer(cust_No), Foreign Key(addr_Owner) references employee(emp_No));

    Am I on the right track?
    Thanks again for your time and help!

    Originally posted by r937
    see the thread Multiple Possible Parents

    or see this article: Supertypes and Subtypes

    your supertype is "entities which have addresses"

    you have two subtypes: customer and employee

    each of these subtype tables should have the supertype id as its primary key, as well as being a foreign key to the supertype table

    then your address table can have a foreign key to the supertype table too

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, not the right track

    a foreign key can reference only one primary key

    you cannot have

    , Foreign Key(addr_Owner) references customer(cust_No)
    , Foreign Key(addr_Owner) references employee(emp_No)

    in the address table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    6
    Hi

    From reading the other post, am I right to think that I can have the address table have cust_no and emp_no fields, i.e.

    create table address(addr_No int not null, cust_no int not null, emp_no int not null, blah, blah, primary key(addr_No),
    index(cust_No), index(emp_No),
    Foreign Key(cust_No) references customer(cust_No),
    Foreign Key(emp_No) references employee(emp_No));

    Thanks again!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can do that

    i wouldn't want to be around when you have to rewrite your code to accommodate a third entity having an address...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    6
    I agree, I also would not want to be there
    my 1st design was using link tables:-
    Customer_address, Employee_Address, etc to get around this, then I started thinking about the address_owner field. I still am not sure why it is not acceptable to have address_owner foreign key link to both customer and employee tables - I obviously need to spend more time on this forum and read more on dbs..
    Best Regards

    Originally posted by r937
    yes you can do that

    i wouldn't want to be around when you have to rewrite your code to accommodate a third entity having an address...


Posting Permissions

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