Confuse in FK

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

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.

you have asked a couple of simple questions which have very lengthy and complex answers :slight_smile:

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

Hi r937, Thank you for the reply…okay i will try the unique index. is that differ from index ?

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.

don’t… just let it continue its merry way

if you expect more than 4 billion rows, let me know :slight_smile:

Thank you so much r937 for helping me… :slight_smile:

is that the limit 4billion rows?

no, the limit is 9 quadrillion

Thank you r397 now i know the limit of auto increment… :slight_smile: