Querying Microsoft SQL Server 2012 70-461 book

Question

One of the questions in the book ask the following:

Test the check constraint CHK_Products_unitprice in the Production.Products table by using the same kind of logic you used in the Lesson 2 exercise. Try to insert a new row with all valid columns, but use a negative unitprice of -10. Drop the check constraint. Retry the insert. Try to add the check constraint back into the table. Update the inserted row so that it has a positive unitprice. Now try adding the check constraint back into the table. Would you be able to add the check constraint back into the table if there were no rows? Why?

I am confused what it is asking? Of course you should be able to add a constarint when all rows does break the constraint?

Thanks

Answer 1

Basically this is just a round-about way of saying that you can only add a check constraint to a table if none of its existing records would fail that constraint.

In this case you have a check constraint to say that the unit price must be positive. That seems sensible, otherwise you'd be paying people to take the product!

So if all the unit prices in your table are positive, you can add the constraint. But if one or more of the unit prices is negative, SQL Server will not let you add the constraint (since you're basically saying "we know this to be true for all the data" about something that isn't true).

The last bit of the question in the book is asking whether you would be able to add the constraint if there were no rows of data in the table. That's the same as asking whether you can add the constraint if it does not conflict with the existing data, which we already know the answer to.