10

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?

2 Answers 2

10

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.

6
  • I heard that transactions are implicit in MySql. Do I need to use explicit transactions for each query or just for concurrent processes?
    – xyz
    Nov 3, 2014 at 20:19
  • 1
    @xyz Having implicit COMMIT is not the same thing as never having or wanting to control you transaction scope. When you are writing into multiple tables in a single logical unit of work you will want to control your transactions explicitly
    – Joel Brown
    Nov 4, 2014 at 15:28
  • @JoelBrown will you please say what is @@IDENTITY and how it's comes
    – siddhesh
    Mar 30, 2015 at 7:38
  • @siddhesh - @@IDENTITY is SQL Server. I have corrected this answer to reflect MySQL, which is one of the OP's tags. The MySQL equivalent of @@IDENTITY is LAST_INSERT_ID().
    – Joel Brown
    Mar 30, 2015 at 12:08
  • What would be the best way to manage this if we were inserting a huge amount of records ? In Postgres or MSSQL, I would have to use something like PL/PGSQL or MSSQL equvivalent ?
    – theMyth
    Feb 5 at 12:05
-1
     ---Method 1 using Ident_Current 

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

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

        Insert INTO trips_places_asc (trip_id,place_id) values
        (Ident_Current('trips'), Ident_Current('places'))

   COMMIT TRAN
      ---Method 2 Using Scope_Identity 

        Declare @PlaceId int ,   @tripid int
        INSERT INTO places (place_name)
        VALUES ('XYZ')
        set @PlaceId = SCOPE_IDENTITY()
        INSERT INTO trips (trip_name)
        VALUES ('MyTrip')
        set @tripid = SCOPE_IDENTITY()
        Insert INTO trips_places_asc (trip_id,place_id) values
        (@tripid, @PlaceId)
1
  • 1
    the question is about MySQL. Not about SQL-Server. And you are missing several ; Mar 30, 2015 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.