Bulk insert and the Foreign Key mystery

Today i ran into a weird thing. One of our clients found out that with using SAP Data Services (an ETL tooling by SAP) where you can use the bulk load setting, foreign keys on a table got disabled.

Demo

To test this out, i’ve created a small demo

CREATE TABLE BASE_TABLE
( ID INT IDENTITY(1,1) PRIMARY KEY,
	SUSPICIOUSCONTENT VARCHAR(20) NULL,
	RANDOMINTS INT NOT NULL
)

CREATE TABLE REF_TABLE
( ID INT ,
	OTHERCONTENT VARCHAR(20) NULL,
	RANDOMINTS INT NOT NULL 
	FOREIGN KEY (ID) REFERENCES BASE_TABLE(ID)
	)

The above code creates the two tables we need for the demo. The second one has a reference to the first one. Now we fill the first table with some data:

INSERT INTO BASE_TABLE (SUSPICIOUSCONTENT, RANDOMINTS)
SELECT TOP 20 CAST(text as varchar(20)), message_id 
FROM sys.messages

Now we have data in the base table, time to insert some data in the referencing table. First of all, i’ll use the BULK INSERT statement to read a CSV file which contains the same data; the ID’s are supplied within the csv file:

Without having done anything, my foreign key is enabled:

select name,
		type_desc,
		is_disabled,
		is_not_trusted
from sys.foreign_keys

Now, bulk insert the data:

BULK INSERT REF_TABLE
FROM 'C:\Temp\test.csv'
WITH (FORMAT='CSV');

This code lets me bulk insert the data from the CSV file. SSMS returns 20 rows affected. Now let’s chech the FK status again with the code shown earlier:

Now, the foreign key is enabled, but it’s not trusted. The errorlog will not show you this. There’s no mention of the foreign key losing it’s trust. It’s just… gone. I checked the default extended events as well, no mention there.

Impact?

What’s the impact of a foreign key that’s enabled but not trusted? SQL Server doesn’t know if the data can be trusted and might think of creative query plans to be sure. This will hurt performance.

What can we do?

The above code has to be changed, you need to rebuild the trust in your foreign key. One option, suggested by Brent Ozar, is to disable the foreign key, load the data, and enable your key again. If you’re not comfortable with that, you can import the data and add the following line of code:

ALTER TABLE REF_TABLE WITH CHECK CHECK CONSTRAINT FK__REF_TABLE__RANDO__37A5467C

This will check the foreign key and rebuild the trust in it, if it can be trusted. If it can’t you have to make sure the data is ok.

If you’re using the bcp command (like my SAP Data Services tooling seems to be doing), you can add the -h flag. This flag allows load hints to mitigate the issue. The command will look something like this:

BCP [DEMODB02].dbo.[REF_TABLE] in C:\temp\test.csv -S localhost\VANTAGE -T -c -t"," -h "CHECK_CONSTRAINTS"

After this, if we check the foreign keys, the are still trusted.

Thanks for reading!