Controlling Constraint Checking by Bulk Import Operations
When bulk importing data, you can control whether CHECK 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.
Note
If you ignore constraints during a bulk-import operation, data that violates existing constraints can be inserted into the table. Therefore, the constraint on the table is marked as is_not_trusted in the sys.check_constraints catalog view (for more information, see sys.check_constraints (Transact-SQL)). At some point, you will need to check the constraints on the entire table.
If the table was nonempty before the bulk import operation, the cost of revalidating the constraint may exceed the cost of applying CHECK 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 constraints, however, the INSERT ... SELECT * FROM OPENROWSET(BULK...) statement lets you override the checking of the CHECK constraints. For information on these constraints, see CHECK Constraints.
Note
Only the CHECK constraints can be disabled. You cannot disable UNIQUE, PRIMARY KEY, FOREIGN KEY, or NOT NULL constraints.
The following table summarizes the table hint for ignoring CHECK 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 Hints (Transact-SQL).
Verifying that Data Was Imported
If you ignore constraints in a bulk-import operation, you can later identify imported table rows that violate constraints by checking the imported data manually. To check the data manually, you can Transact-SQL queries or stored procedures that test the constraint conditions.
Note
To learn whether a table is trusted, see the is_not_trusted column in the sys.check_constraints catalog view. For more information, see sys.check_constraints (Transact-SQL).