tSQLt.FakeTable DOES NOT remove the original table

tSQLt is a great tool for simplifying database testing but it does come with a few gotchas. I got stuck on a failed test today that took me a while to identify the root cause. Let me take you through the journey. (Object names modified for the post)

The error I encountered was:

[test].[test book] failed: (Error) The INSERT statement conflicted with the FOREIGN KEY constraint "FK__book__publisher__31E413FD". The conflict occurred in database "anz_summix", table "dbo.tSQLt_tempobject_da8838ee37ed44daa755d58f38d078e3", column 'name'.[16,0]{test book,8}

In hindsight the temp table name was a dead giveaway but at the time I was like it’s a temp table made by tSQLt and who cares about temp tables, right? I went and looked at the tables involved:

book (foreign key table)
|
|FK__book__publisher__31E413FD
|
publisher (primary key table)

Both tables were empty. The foreign key was there. Everything looked normal. So then I took a look at the test proc (simplified for the post):

create proc test.[test book]
as

exec tSQLt.FakeTable 'publisher'

insert dbo.publisher (name)
values ('Microsoft')

insert book (name, publisher)
values ('SQL Server For Dummies', 'Microsoft')

return 0
go

I observed that the publisher table was faked which means it didn’t have any constraints on it. Data was inserted in the right order, primary key table first and then the foreign key table. Everything still looked ok so far.

What I failed to realise immediately at the time was that when tSQLt fakes a table, it doesn’t remove the original table, it simply renames it according to the source code:

EXEC tSQLt.Private_RenameObjectToUniqueName @SchemaName, @TableName, @NewNameOfOriginalTable OUTPUT;

This fact was not made clear in the official description:

FakeTable creates an empty version of the table without the constraints in place of the specified table.

That’s what the temp table was in the error message, the original table in disguise. The relationship actually looked like the diagram below during the execution of the test:

book (foreign key table)
|
|FK__book__publisher__31E413FD
|
tSQLt_tempobject_da8838ee37ed44daa755d58f38d078e3 (primary key table)

Once I realised what’s going on, the fix was quite easy. In my case I just needed to remove the FakeTable line as it was not required after all. Another option was to remove the foreign key constraint and that would also work.

Another note, you should always run tests at least once in an empty database environment. By empty I mean a freshly deployed database with no production data. The test I had trouble with was actually passing in our CI environment. That’s because the primary key table already had all the necessary data in it. It only showed up on my radar when I executed it against my local environment.