Microsoft® SQL Server® 2008 Bible

Chapter 16. Modification Obstacles

IN THIS CHAPTER

• Avoiding and solving complex data-modification problems

• Primary keys, foreign keys, inserts, updates, and deletes

• Deleting duplicate rows

• Nulls and defaults

• Trigger issues

• Updating with views

Some newsgroup postings ask about how to perform a task or write a query, but another set of postings ask about troubleshooting the code when there is some problem. Typically, SQL Server is working the way it is supposed to function, but someone is having trouble getting past what's perceived to be an obstacle.

This chapter surveys several types of potential obstacles and explains how to avoid them. In nearly every case, the obstacle is understood—it's really a safety feature and SQL Server is protecting the data by blocking the insert, update, or delete.

As Table 16-1 illustrates, INSERT and UPDATE operations face more obstacles than DELETE operations because they are creating new data in the table that must pass multiple validation rules. Because the DELETE operation only removes data, it faces fewer possible obstacles.

Data Type/Length

Column data type/length may affect INSERT and UPDATE operations. One of the first checks the new data must pass is that of data type and data length. Often, a data-type error is caused by missing or extra quotes. SQL Server is particular about implicit, or automatic, data-type conversion. Conversions that function automatically in other programming languages often fail in SQL Server, as shown in the following example: ...