Imagine you have two tables Employee and EmployeeAddress. The table schema is given below.
- employeeid int not null auto increment
- name varchar(100)
- age int
- designation varchar(100)
- addressid int not null auto increment
- employeeid int not null (foreign key relationship with Employee table)
- Address1 varchar(100)
- Address2 varchar(100)
Here employeeid in EmployeeAddress table has foreign key relationship with Employee table. Suppose you have to insert employee information in two tables. You have to first insert in Employee table, get the employeeid column value of last inserted record and insert in EmployeeAddress table. How do you get the value of last inserted record? You may be tempted to use the following SQL query.
SELECT MAX(employeeid) FROM Employee;
The above query returns the value of employeeid of last inserted record in Employee table because employeeid is an auto increment column. This seems to be OK, but suppose two threads are executing insert operation simultaneously, there is a chance that you get wrong id of last inserted record! Don’t worry, MySQL provides a function which returns the value of auto increment column of last inserted record.
LAST_INSERT_ID() is always connection specific, this means even if insert operation is carried out simultaneously from different connections, it always returns the value of current connection specific operation. So you have to first insert record in Employee table, run the above query to get the id value and use this to insert in second table.
Note: this works only for auto increment column.