6

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?

7

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.

| improve this answer | |
  • 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 '14 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 '14 at 15:28
  • @JoelBrown will you please say what is @@IDENTITY and how it's comes – siddhesh Mar 30 '15 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 '15 at 12:08
-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)
| improve this answer | |
  • 1
    the question is about MySQL. Not about SQL-Server. And you are missing several ; – ypercubeᵀᴹ Mar 30 '15 at 20:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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