Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Modifying Data with SQL Handling Errors When Manipulating Data Rolling Back from Transactions

Sergio Cruz
Sergio Cruz
15,550 Points
Posted by Sergio Cruz
Sergio Cruz
15,550 Points

ROLLBACK not working as expected

I'm trying to duplicate the rollback example in this video but I keep on getting — Error: cannot start a transaction within a transaction.

This is my code: 

BEGIN;

INSERT INTO reviews VALUES (NULL, 12, "lion", "Fantastic", 5);

SELECT * FROM reviews;
ROLLBACK;

The reviews table has 5 columns: id, movie_id, username, review, rating

What am I not understanding?

3 Answers

Vanessa Schwegman
Vanessa Schwegman
3,587 Points

My understanding: Auto-increment must be turned on if you're working outside of SQL Playgrounds if you're hoping to get the next ID from NULL.

I also seem to recall the video mentioning that transactions aren't enabled in SQL Playgrounds (assuming you're not working outside of it with the same tables).

The error message you see is interesting: "Error: cannot start a transaction within a transaction." Maybe Treehouse's implementation of SQL Playgrounds rolls back all of our changes during our practice (which is why changes aren't saved and we can easily reset them). If so, then perhaps all of our practice is run as a transaction that is rolled back, and thus you can't run another transaction within a transaction that isn't yet committed or rolled back (which may be why transactions aren't enabled for us). However, I'm just speculating!

Jeff Pierce
Jeff Pierce
18,077 Points
Jeff Pierce
Jeff Pierce
18,077 Points

This is more of a set of questions than an answer.

  • Can your table have a NULL id?
  • Why would you want to insert a NULL id?
  • Have you tried it with a unique id?
kevin hudson
PLUS
kevin hudson
Courses Plus Student 11,987 Points
kevin hudson
kevin hudson
Courses Plus Student 11,987 Points

What Jeff is trying to say is that when you Start a new SEED to commit a table of data you must put a unique ID with that row of data else you have an error. I believe the error message you have is because you did not COMMIT your changes yet else there is nothing there.