Beginning ASP.NET 3.5: In C# and VB

A.11. Chapter 11

A.11.1.

A.11.1.1.

A.11.1.1.1. Exercise 1 solution

The DELETE statement fails because there is a relationship between the Id of the Genre table and the GenreId of the Review table. As long as this relationship is in effect, you cannot delete genres that still have reviews attached to them. To be able to delete the requested genre, you need to delete the associated reviews first, or assign them to a different genre using an UPDATE statement.

A.11.1.1.2. Exercise 2 solution

The relationship between the Genre and Review tables is a one-way relationship. The relationship enforces that the GenreId assigned to a review must exist as an Id in the Genre table. At the same time, it blocks you from deleting genres that have reviews attached to them. However, the relationship doesn't stop you from deleting records from the Review table.

A.11.1.1.3. Exercise 3 solution

To delete reviews with an Id of 100 or less, you need the following SQL statement:

DELETE FROM Review WHERE Id <= 100

A.11.1.1.4. Exercise 4 solution

Before you can delete the genre, you need to reassign the existing reviews to a new genre first. You can do this with the following UPDATE statement:

UPDATE Review SET GenreId = 11 WHERE GenreId = 4

This assigns the GenreId of 11 to all reviews that previously had their GenreId set to 4. This in turn means that the genre with an ID of 4 no longer has any reviews attached to it, so you can remove the genre with the following SQL statement:

DELETE FROM Genre ...