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).