DATA CHECKS V7.0
SQL Server provides many mechanisms for data checking and maintaining 
data integrity. When you write a SQL application, it is important to 
know the order of these checks and how they affect your approach to 
process execution and error handling.

There is a seven-step approach that SQL Server follows before a 
transaction is committed to the database. These seven steps insure that 
a transaction is valid.

Assume the given table layout:

CREATE TABLE Customer ( 
CustomerID INT IDENTITY(1,1) NOT NULL CONSTRAINT  
pk1 PRIMARY KEY, 
Fname VARCHAR(20) NOT NULL, 
Lname VARCHAR(30) NOT NULL,  
SSN VARCHAR(10) NOT NULL CONSTRAINT  
df1 DEFAULT 'Private',  
ZipCode CHAR(5) NOT NULL CONSTRAINT  
fk1 FOREIGN KEY (ZipCode) REFERENCES ValidZip(ZipCode), 
Sex CHAR(1) NULL CONSTRAINT  
chk1 CHECK (Sex = 'M' or Sex = 'F') )

1. If you have a column default defined on column SSN, SQL Server 
evaluates this integrity step first; i.e., if a value is not provided 
on an insert statement to the column that has a default, then SQL 
Server supplies the default value (Private) and proceeds to step number 
two.

2. The next step involves SQL analyzing NOT NULL violations (Fname, 
Lname). If a particular NOT NULL column is violated either by supplying 
a NULL to a 'NON NULLABLE' column or an expression returns NULL on a 
'NON NULLABLE' column, then this step fails and the transaction is 
rolled back.

3. If this step has been reached, then SQL begins to evaluate the check 
constraints; i.e. if there is a domain of 'M' or 'F' for the (Sex) 
column of a table, then the value being supplied must be 'M', 'F,' or 
NULL based upon the above scenario. 

4. When steps 1-3 have been checked, SQL Server then begins to evaluate 
the FOREIGN KEY constraint of referencing tables, i.e. a table that is 
referring to the Customer table (not shown).

5. When steps 1-4 have been checked, SQL Server then begins to evaluate 
the FOREIGN KEY constraint of referenced tables, i.e., a table that is 
referenced by the Customer table (ValidZip, not shown).

6. The last step in constraint validation takes place here when SQL 
Server checks for UNIQUE and PRIMARY KEY validation, i.e., 
(CustomerID).

7. When all of the previous steps complete without error, then SQL 
Server fires triggers.

This order my help you in your application programming efforts; if you 
know the order of checking, then you may spend less time debugging SQL 
code.

-------------------------------------------