Controlling Constraint Checking by Bulk Import Operations
When bulk importing data, you can control whether CHECK and FOREIGN KEY constraints are enforced by the bulk-import operation. A constraint is a business rule that is put on a column to define its valid values; for example, a constraint for a column containing telephone extensions might require the form ####. Defining and checking constraints are the standard mechanism for enforcing data integrity. Microsoft recommends that normally you use constraint checking during an incremental bulk import.
Sometimes you might want to ignore constraints. An example scenario is if your input data contains rows that violate constraints. By ignoring the constraints, you can load the data and then use Transact-SQL statements to clean up the data.
If the table was nonempty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK and FOREIGN KEY constraints to the incremental data. After you import problematic data, you must use Transact-SQL to clean up the imported data.
Important
When constraints are disabled, a schema modify lock might be taken to update the metadata. This can interfere with other commands (such as an online index build) or transactions. For example, a snapshot isolation transaction accessing the target table might fail due to concurrent DDL changes.
For more information on constraints, see Constraints.
During a bulk-import operation, constraint-checking behavior depends on the command used for the operation. By default, the bcp command and the BULK INSERT statement ignore constraints. In contrast, for an INSERT ... SELECT * FROM OPENROWSET(BULK...) statement, the default is to check constraints.
The following table summarizes the default constraint-checking behavior of the bulk import commands.
| Command | Default behavior |
|---|---|
bcp |
Ignore constraints |
BULK INSERT |
Ignore constraints |
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
Check constraints |
Each of the bulk-import commands provides a qualifier that allows you to change how constraints are handled, as explained in the following sections.
Checking Constraints with bcp or BULK INSERT
By default, constraints are ignored during a bulk-import operation that is performed by the bcp command or BULK INSERT statement.
The bcp command and BULK INSERT statement allow you to specify that constraints are to be enforced during a bulk-import operation. Enforcing constraints slows the bulk-import operation but ensures that all inserted data does not violate any existing constraints. The following table summarizes the qualifiers you can use to specify enforcement of constraints during a bulk-import operation.
| Command | Qualifier | Qualifier type |
|---|---|---|
bcp |
-h"CHECK_CONSTRAINTS" |
Hint |
BULK INSERT |
CHECK_CONSTRAINTS |
Argument |
For more information, see bcp Utility and BULK INSERT (Transact-SQL).
Ignoring Constraints in INSERT ... SELECT * FROM OPENROWSET(BULK...)
By default, INSERT checks CHECK and FOREIGN KEY constraints. However, the INSERT ... SELECT * FROM OPENROWSET(BULK...) statement lets you override the checking of the CHECK and FOREIGN KEY constraints. .
Note
You cannot disable UNIQUE, PRIMARY KEY, or NOT NULL constraints.
The following table summarizes the table hint for ignoring CHECK and FOREIGN KEY constraints.
| Command | Qualifier | Qualifier type |
|---|---|---|
INSERT ... SELECT * FROM OPENROWSET(BULK...) |
WITH (IGNORE_CONSTRAINTS) |
Table hint |
The following example illustrates how to use this qualifier. For more information about the IGNORE_CONSTRAINTS hint, see Table Hint (Transact-SQL).
Verifying that Data Was Imported
If you ignore constraints during a bulk-import operation, data that violates existing constraints can be inserted into the table. Therefore, each ignored constraint on the table is marked as is_not_trusted in the sys.check_constraints or sys.foreign_keys catalog view. At some point, you will have to check the constraints on the whole table.
You can identify imported table rows that violate constraints by checking the imported data manually by using Transact-SQL queries or stored procedures that test the constraint conditions.
See Also
Other Resources
bcp Utility
BULK INSERT (Transact-SQL)
DBCC CHECKCONSTRAINTS (Transact-SQL)
INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
SELECT (Transact-SQL)
Table Hint (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|