IN THIS CHAPTER
Avoiding and solving complex data-modification problems
Primary keys, foreign keys, inserts, updates, and deletes
Deleting duplicate rows
Nulls and defaults
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,
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.
Column data type/length may affect
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: ...