Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: Auto-increment on FK

    Finally figured how to implement foreign key relationships in MySQL, my question is should I set the foreign key fields in the child table to auto-increment, or is this already dealt with because they are A_I is set in the parent table?

    For instance:
    UserTbl
    ---------
    userID [pk]
    firstname
    lastname
    departmentID [fk]

    ---------
    DepartmentTbl
    ----------
    departmentID [pk]
    departmentTitle

    From the example above, the departmentID in the UserTbl be set to auto-increment or is this dealt with through the FK relationship?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JHamill View Post
    ... should I set the foreign key fields in the child table to auto-increment, or is this already dealt with because they are A_I is set in the parent table?
    neither

    first of all, you shouldn't make it an auto_increment, it'll get out of step immediately

    but it isn't "taken care of" by the parent table, either

    you actually have to specify the parent primary key value as a value to be inserted when you insert a child row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009
    Posts
    5
    Thank you for your quick response again r937.

    Is this done by auto cascade on update?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JHamill View Post
    Is this done by auto cascade on update?
    no, you have to do it with application code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    5
    Oh I see now - should have noticed that beforehand really.

    Thanks again.

Posting Permissions

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