SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Confuse in FK

  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Post Confuse in FK

    Hi, Can i ask when to put FK (foreign key) in the table ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    Hi, Can i ask when to put FK (foreign key) in the table ?
    either in the CREATE TABLE statement, or afterwards, in an ALTER TABLE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi r937 thank you for the reply...I have some question regarding in the phpmyadmin i created a table with

    id auto_increment
    employee_code varchar(5)

    as far as i know that, a field which is auto_increment is a primary key, and in my php code i trapped the employee_code that will not insert duplicate.
    is that okay to trapped employee_code even though i have an auto increment field ?

    Thank you in advance.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you have asked a couple of simple questions which have very lengthy and complex answers

    first of all, creating an auto_increment PK is not wrong, but not always the best solution -- sometimes, some other column which you know will be unique could be a better choice (if you want to research this, search natural key)

    second, if you do use an auto_increment, and you have a column like employee_code which is supposed to be unique, it is better to let the database control the uniqueness, instead of using application logic to "trap the employee_code that will not insert duplicate"

    so you should declare a UNIQUE index on employee_code, and then do not do the SELECT before you do your INSERT, rather, just do the INSERT without checking yourself, and reject the submission only if you get an error code after the INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi r937, Thank you for the reply...okay i will try the unique index. is that differ from index ?

  6. #6
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    Thank you it's working i add the unique index in employee_code.and i do an insert statement like what you told me,by the way how can i reset the auto_increment failed.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jemz View Post
    ...by the way how can i reset the auto_increment failed.
    don't... just let it continue its merry way

    if you expect more than 4 billion rows, let me know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much r937 for helping me...

    is that the limit 4billion rows?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, the limit is 9 quadrillion
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,106
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you r397 now i know the limit of auto increment...


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
  •