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

Changed content:
  • Revised topic to indicate that CHECK and FOREIGN KEY constraints are controlled together.