Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Question Unanswered: Inserting foreign keys

    This is my first post. I hate to be asking for help right out of the starting gate but I have hit a wall.

    I am in the process of creating what I hope will be a well designed relational database. It is MySQL and the tables are INNODB. 15 tables in all.

    Example:

    3 tables: Event, Participant, Address

    User enters all data on form.

    The Event table is the main table.

    How do I get the PK from one table entered as a FK in another?

    Do I want FK's from Participant (part_id)and Address (add_id) added to the Event table?

    Or do I want the Event (event_id) fk added to the other tables?

    Thanks
    Last edited by mtbadfish; 12-30-06 at 21:25.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mtbadfish
    How do I get the PK from one table entered as a FK in another?
    if you are using auto_incrtement columns, you would do this using the LAST_INSERT_ID() function
    Quote Originally Posted by mtbadfish
    Do I want FK's from Participant (part_id)and Address (add_id) added to the Event table?

    Or do I want the Event (event_id) fk added to the other tables?
    it does get confusing sometimes

    okay, here's a rule of thumb that should help you

    if an event can have multiple participants, but a participant have only one event, then event-to-participant is a one-to-many relationship, and the foreign key goes into the "many" table, the participant

    if a participant can have multiple events, but an event have only one participant, then participant-to-event is a one-to-many relationship, and the foreign key goes into the "many" table, the event

    if an event can have multiple participants, and a participant have multiple events, then event-to-participant is a many-to-many relationship, and you need a 3rd "relational" table between them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are you asking how to move the value of a PK from a parent table, and putting that value into an FK column in the child table, or are you asking how do you delcare the relationship between the PK column and the FK column? I can't tell which question you are asking, and they get rather different answers.

    As far as the values goes, you take the value from the PK and insert it into the FK, just as R937 has suggested. If you are using auto_increment then the LAST_INSERTED_ID() function will help here.

    If you are looking for how to declare the relationship, check the MySQL manual for CREATE TABLE and pay special attention to the FOREIGN KEY declaration.

    -PatP

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    also note that foreign key relationships are only enforced in the InnoDB table type and not the default MyISAM table type.

    You can declare them in the latter but you can insert incorrect values in the secondary tables and your CASCADING deletes and updates won't work.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's a good point about MyISAM. While I understand that MyISAM is an important feature of MySQL, the fact that it allows a user to declare referential integrity, but then won't enforce that integrity is something I have a hard time swallowing. I consider that to be an unforgivable omission, one that keeps me from considering MySQL as a relational database engine.

    You can certainly implement a relational database using MySQL, and if you use InnoDB, you can have a relational database engine, but if you use MyISAM then you are "on your own" for ensuring basic relational integrity.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, have you never seen an application which requires just one, albeit large, table? so that you need the speed of an indexed search rather than a flat file? myisam is perfect for that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh I agree. There are many applications that MyISAM is perfectly adequate for, and for those applications it is a fine tool. It is fast, simple, and has a lot to recommend it.

    You can develop a relational application to run against text files, and MyISAM will support that relational application better than a text file will. MyISAM is still not able to provide full support of what I consider the basic requirements for a relational database (relational integrity) even though you can certainly run a relational application against a MyISAM data source.

    -PatP

  8. #8
    Join Date
    Dec 2006
    Posts
    2
    Thanks for the help. Sorry for my delay in responding - been away.

    The tables are INNDB. I will get a little practice in and see how it goes.

Posting Permissions

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