Unit Testing Databases with tSQLt Part 10 – testing a foreign key's ON DELETE or ON UPDATE actions

by data-centric on March 20, 2012

In Part 9, we looked at basic unit testing of a foreign key ensuring that basic referential integrity was properly enforced. In this post, we continue writing tests for foreign keys, delving in to cascading deletes (or not cascading as the case may be). We’re using deletes in this article but the same approach and logic could be applied to the testing of cascading updates.

We will continue to use my re-development of Log4TSql, the open source logging library for SQL2005+, as the basis for these examples so here is a reminder of what our schema looks like:

You can see that we have a parent-child relationship between [Journal] and [JournalDetail] and as both of these tables will be subject to periodic cleanup, one way to implement this would be via a foreign key that cascades any deletes from the parent table, thus increasing the amount of code NOT written. I have also generally found this technique to be faster than deleting from each table in turn (subject to the foreign key column in the child table being indexed). The first test that we are going to write requires that one or more rows are inserted into [Journal] on which the primary key column is also the IDENTITY column.

One of the aims when writing any unit test should be that on completion of the test, and regardless of the outcome, we should leave the database in the exact same state as before the test. One of the best ways to fully isolate such unit tests from the database is to fake the tables under test. However at the time of writing (v1.0.4413.31717), whilst tSQLt does have limited support for applying foreign key constraints to fake tables there is currently no support for mocking specific primary or unique keys in the same way.

Additionally, custom CASCADE properties are not carried across to the mock foreign key so we can’t use [FakeTable] or [ApplyConstraint] for this type of test. This isn’t as big an issue as it might seem because tSQLt automatically runs each unit test within its own transaction which is always rolled back on completion of the test. This means that, in terms of data, we can guarantee that we meet that goal of returning the database to its pre-test state.

Where this can get a little more complicated is when the primary key of the parent table is also an IDENTITY column. As Sebatian Mien (blog | twitter) points out in “The Gap In The Identity Value Sequence“, identity values are only used once and, in a multi-user system, business logic should not depend on unbroken sequences. This means that if a row is inserted into [Journal] and then removed as part of a rollback (within a unit test or otherwise), that ID is not re-used – leaving gaps in the identity sequence. It should be obvious that given how SQL Server works with IDENTITY values within transactions, you should not write business logic that relies on unbroken auto-increment sequences.

So the only way to write this test is against the production tables rather than copies. Fortunately, there are no dependencies on unbroken ID sequences in Log4TSql so we can be comfortable inserting test rows into [Journal] and [JournalDetail] whilst leaving tSQLt to handle the clean-up and remove them on test completion.

As a side note, I learnt recently from Dennis Lloyd Jr (blog | twitter), one of the authors of tSQLt, that when you use [ApplyConstraint] to add a foreign key to a faked table, tSQLt will ensure that there is a corresponding unique key on the table being referenced. This means that, in the code snippit below, although we can’t apply an explicit primary or unique key on the fake [Severity], under the hood, a unique key is added to the parent table so that the specified foreign key can be applied to the fake [Journal]. This is not well documented but definitely worth remembering.

EXEC tSQLt.FakeTable ‘log4.Severity’;
EXEC tSQLt.FakeTable ‘log4.Journal’;
EXEC tSQLt.ApplyConstraint ‘log4.Journal’, ‘FK_Journal_Severity’;

However, in this case, we want to test that this foreign key cascades on delete so we have to write our test against the real table.

In this test, we start by using [JournalBuilder] to quickly and simply add two valid new rows to [Journal], one of which we will delete later and the other we will retain. [JournalBuilder] is an example of the Test Data Builder pattern for T-SQL which I explain here (and give a detail example of usage under the heading “Preparing sample data easily” here). Next, we insert several rows into [JournalDetail] for each Journal entry that we just created. We can then use the value of @JournalIdToRetain to identify the rows we expect to be retained in [JournalDetail] after the DELETE – this populates our “expected” table.

CREATE PROCEDURE [TestJournalDetailTable].[Test_JournalDetail_delete_is_cascaded_from_Journal]
IF OBJECT_ID(N’tempdb..#expected’) > 0 DROP TABLE [#expected];

–! Setup a couple of Journal entries
DECLARE @JournalIdToRetain int; EXEC [TestDataBuilder].[JournalBuilder] @JournalId = @JournalIdToRetain OUT
DECLARE @JournalIdToDelete int; EXEC [TestDataBuilder].[JournalBuilder] @JournalId = @JournalIdToDelete OUT

–! Add JournalDetail rows for each Journal entry
INSERT [log4].[JournalDetail] ([JournalId], [Progress]) VALUES (@JournalIdToRetain, ‘A’);
INSERT [log4].[JournalDetail] ([JournalId], [Progress]) VALUES (@JournalIdToRetain, ‘B’);
INSERT [log4].[JournalDetail] ([JournalId], [Progress]) VALUES (@JournalIdToDelete, ‘C’);
INSERT [log4].[JournalDetail] ([JournalId], [Progress]) VALUES (@JournalIdToRetain, ‘D’);
INSERT [log4].[JournalDetail] ([JournalId], [Progress]) VALUES (@JournalIdToDelete, ‘E’);

–! What should our data look like after deleting the Journal entry
, JournalId
, Progress
INTO #expected
FROM [log4].[JournalDetail]
WHERE JournalId = @JournalIdToRetain;

–! Exercise
DELETE [log4].[Journal] WHERE JournalId = @JournalIdToDelete;

–! Assert
EXEC tSQLt.AssertEqualsTable ‘#expected’, ‘[log4].[JournalDetail]’, ‘DELETE from [Journal] did not CASCADE as expected’;

Finally, we delete the row in [Journal] for @JournalIdToDelete and then use [AssertEqualsTable] to compare the contents of our [#expected] with what’s left in [JournalDetail]. For reference, this is what the foreign key definition should look like to pass this test:

ALTER TABLE [log4].[JournalDetail] ADD
REFERENCES [log4].[Journal]

As the foreign key declaration gives us the option to specify what happens when the parent row is deleted, we’ve had to write a test to prove that “CASCADE” works as expected. So it stands to reason that we should also write a test to prove the case when the delete option is “NO ACTION”.

In this next test, we want to ensure that if a user attempts to delete an Exception that is referenced by a Journal entry, the attempt will fail and an error will be raised. Like the previous test, we start by using a TDB sproc [ExceptionBuilder] to add two valid rows to [Exception], one of which we will try to delete later and the other we will retain. We then use [JournalBuilder], to add one [Journal] row for each of the exceptions we have recorded. [JournalBuilder] also automatically takes care of any referential integrity requirements adding stub rows to [Module] and [Task]. At the risk of repeating myself, notice how easy it is for us to add rows to these tables, specifying only the values we’re interested in, by using the Test Data Builder pattern. In my opinion, this pattern really does simplify data set up when writing tests.

It is also true to say that, even without using the TDB pattern, tSQLt makes data setup easy. Columns on faked tables all allow NULL so you could just insert values into the columns that you want to test and ignore the rest. It just depends on your preference and the complexity of any related setup requirements.

Next, we try deleting one of the Exception rows and we do this within a TRY…CATCH… construct so we can catch the error number gracefully. What we want to see here is a test failure due to the fact that the error number is not the expected 547 rather than an error. If I run this test without the error handling tSQLt reports an error like this:

|Test Execution Summary|

|No|Test Case Name                                                      |Result |
|1 |[TestJournalTable].[Test_delete_Module_throws_error_for_Journal]    |Success|
|2 |[TestJournalTable].[Test_delete_Severity_throws_error_for_Journal]  |Success|
|3 |[TestJournalTable].[Test_delete_Task_throws_error_for_Journal]      |Success|
|4 |[TestJournalTable].[Test_Journal_allows_only_referenced_ModuleId]   |Success|
|5 |[TestJournalTable].[Test_Journal_allows_only_referenced_SeverityId] |Success|
|6 |[TestJournalTable].[Test_Journal_allows_only_referenced_TaskId]     |Success|
|7 |[TestJournalTable].[Test_Journal_JournalId_auto_increments]         |Success|
|8 |[TestJournalTable].[Test_Journal_JournalId_is_unique]               |Success|
|9 |[TestJournalTable].[Test_Journal_metadata]                          |Success|
|10|[TestJournalTable].[Test_Journal_table_exists]                      |Success|
|11|[TestJournalTable].[Test_Journal_allows_only_referenced_ExceptionId]|Error  |
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 11 test case(s) executed, 10 succeeded, 0 failed, 1 errored.

Now, I know I’m being pedantic but although I still get alerted to the fact that there is a problem with this test, the information I’m getting tells me there is an ERROR. Whereas in fact it’s just slightly shoddy coding on my part. Putting the DELETE statement in a TRY…CATCH… allows me to handle the actual error gracefully and then go on to do what the test is designed to do, report on whether we get the expected foreign key violation. It is a subtle difference, but an error is telling me something unexpected happened whereas a test failure tells me everything worked but I haven’t written enough code to pass the test yet.

CREATE PROCEDURE [TestJournalTable].[Test_delete_Exception_throws_error_for_Journal]
–! Setup a couple of Exception entries
DECLARE @ExceptionIdToRetain int; EXEC [TestDataBuilder].[ExceptionBuilder] @ExceptionId = @ExceptionIdToRetain OUT
DECLARE @ExceptionIdToDelete int; EXEC [TestDataBuilder].[ExceptionBuilder] @ExceptionId = @ExceptionIdToDelete OUT

–! Add a Journal row for each Exception
EXEC [TestDataBuilder].[JournalBuilder] @ExceptionId = @ExceptionIdToRetain;
EXEC [TestDataBuilder].[JournalBuilder] @ExceptionId = @ExceptionIdToDelete;

DECLARE @expectedError int; SET @expectedError = 547; — FOREIGN KEY constraint violation
DECLARE @actualError int; SET @actualError = 0;

–! Exercise
DELETE [log4].[Exception] WHERE ExceptionId = @ExceptionIdToDelete;
SET @actualError = ERROR_NUMBER();

–! Assert
EXEC tSQLt.AssertEquals @expectedError, @actualError;

So in this post, we looked at testing the ON DELETE option of a foreign key – both “CASCADE” and “NO ACTION”. Although we didn’t cover it here, the same approach can be used to test the ON UPDATE option. in Part 11, we delve deeper into mocking stored procedures and explore how to populate output parameters or add a row to a table without any of the intervening complex logic in the procedure being mocked

Log4TSql is an open source logging framework for SQL Server 2005+ written by Greg M Lucas on behalf of data-centric solutions ltd and licensed for public use under the GNU Lesser General Public License. You can download the latest version of this library along with all the source code and unit tests from sourceforge. Please feel free to use and modify the library as you wish under the terms of the license. I am always interested in hearing how other people or organisations have put this framework to use.

Previous post:

Next post: