Problem with auto increment primary key (MySQL).

Question

I have 2 tables each using other's primary key as a foreign key. The primary keys for both are set to auto_increment.

The problem is, when I try to create and entry into one of the tables, I have no idea what the primary key of the entry is and can't figure out what to put in the other table as a foreign key. What should I do? Do I drop auto_increment altogether and cook up a unique identifier for each entry so I can use it to address the created entries? I'm using PHP, if that's relevant. Thanks.

Answer 1

Every major database going provides a means of finding the ID of the record you just inserted; the idea of auto-incremented IDs wouldn't be very useful otherwise.

In MySQL, you can use mysql_insert_id(). See the PHP function of the same name or the MySQL function it wraps around.

Answer 2

You can use this function: mysql_insert_id in order to get the last id inserted

Answer 3

After the initial insert, the mysql_insert_id() function will return the autoincremented id value so that you can use it in the next insert

Answer 4

mysql_insert_id can give you the value of the last generated value of an autoincrement primary key.

If you're using PDO (which you should), you can use PDO::lastInsertId() instead. This will work for all database systems that are supported by PDO.