Working With Dependencies In SSDT Unit Test: Disable Contstraints

When we run a piece of T-SQL code, it can be dependent on another T-SQL code such as function. For example, stored procedure queries data with only one SELECT command. In this command you can use scalar functions in SELECT clause, table-valued functions or views in FROM clause, scalar functions in WHERE clause (that’s a very bad habit, but it’s possible), etc.

Our T-SQL code can be affected by other database objects such as constraints and triggers. When we insert or update data, these objects can prevent to enter incorrect values, so our command would fail.

Let’s look at how they would impact on database unit testing.
I modify the test case I’ve created earlier in this post and add a simple constraint.

Table with constraint

My test passed because ClientName (‘John Smith’) consists of 10 chars. Now I change to something shorter, and the test failed.

failed test

There’s no error in stored procedure I’ve been testing, it’s a violation of constraint. So to pass this test (I mean to test the functionality of stored procedure) I should eliminate constraint from being applied during the test. It can be done in the following manner:

  • choose an item Pre-test in the dropdown list shown below
Create post-test
  • Click the link Click here to create in the center of Test Designer.
  • Write T-SQL code to disable the constraint.

Alternatively, we can disable all constraints in a table with one command:

Now our test really passed.

pre-test disable constraint
But don’t forget to enable constraint in Post-Test section of unit test.

Leave a Reply

Your email address will not be published. Required fields are marked *