Search code examples
sqlforeign-keyssql-delete

How to write a query for deleting a trip?

Have a difficulties with query. I am new in SQL so trying to make better query for deleting a trip.

Does anyone know how to make it easier in one query?

Thank you

Solution

  • If you want to perform a delete across all your tables in one single statement and you happen to be using a SQL Engine that support CASCADE you might be in luck.

    From your sample query it appears that all tables share a chained PK\FK relationship.

    • Venues through checkpoint_id
    • Checkpoints through day_id
    • Days through trip_id
    • Trips (at the top)

    My inference is that TRIP is the top of the chain. If you specifically define the PK\FK relationship 'CONSTRAINT' which you should for good DB design, then on each table, you can alter the constraint with the option CASCADE. With that done simply deleting a row from trip will automatically delete all other rows below via the defined relationship.

    In other words, with CASCADE and a SQL ENGINE that supports it - you can simply issue the last delete statement and the rest is implied.

    DELETE FROM trips WHERE id = 27

    Check table constraint options here. Pay attention to CASCADE definition. Similar question with explanations here.