MySQL Many-to-Many – How to Insert Values in Junction Table

database-designMySQLrdbmsrelational-theory

I have three tables in database:

 trips(trip_id(pk), trip_name(unique), user_id(fk))

 places(place_id(pk), place_name(unique))

 trips_places_asc(trip_id(fk), place_id(fk))

Since many trips can have many places, I have one junction table as above.

If a user inserts places to the trip, the places will be added to places table and the trip will be associated with the places in trips_places_asc table.

So, if I write a query like: 

INSERT INTO places (place_name)
VALUES ('XYZ')

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

then how to store trip_id and place_id in Junction or Association table trips_places_asc? Will I have to fire two queries?

Best Answer

You want to maintain atomicity of the action of storing a new place and trip, along with the intersection between the two. In MySQL, you do this with a START TRANSACTION / COMMIT transactional wrapper.

You use the MySQL function LAST_INSERT_ID() (See documentation; similar to SQL Server variable @@IDENTITY) to get the value of the most recent auto increment value assigned.

To add a place and a trip and associate the two all at once, you would write code like this:

START TRANSACTION

DECLARE placeKey int

INSERT INTO places (place_name)
VALUES ('XYZ')

SET placeKey = LAST_INSERT_ID()

DECLARE tripKey int

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

SET tripKey = LAST_INSERT_ID()

INSERT INTO trips_places_asc(trip_id, place_id)
VALUES (tripKey, placeKey)

COMMIT

You should add error handling to the above and if an error occurs part way through, use the ROLLBACK command to undo the partially completed work.

You should also read up on LAST_INSERT_ID() to make sure you know how it works. It can give you undesirable results in some cases, such as when you insert multiple rows with a single insert statement.

Related Question