Unit Testing Databases with tSQLt Part 9 – testing a FOREIGN KEY constraint

by Greg M Lucas on 7 March 2012

In Part 8, we looked at testing string searches as part of a WHERE clause and also validating the effect of supplying multiple, cumulative search conditions. In this post, we go back to our DDL tests and explore a couple of approaches to unit testing foreign keys.

If you have been following along with this series you will know that in Parts 1-8, we wrote and tested the exception handling and retrieval logic which is part of Log4TSql, the open source logging framework for SQL Server 2005+ that I have been working on.  The next part of this library needs to support more general logging with a wider variety of options.

So the principle behind this part of the framework is that developers can log one or more events or steps within a stored procedure that they write and in addition to progress and/or completion information, they can also specify a severity, module and task. Severity and Module (A.K.A. procedure) should be fairly self-explanatory and Task is simply a free text string that the developer can choose to supply to group journal entries that have been recorded by different procedures.  This could be useful for example,  when a SQL Agent Job calls multiple stored procedures and Task can be the name of the job.  We also want the option to be able to associate a Journal entry with a particular Exception. This results in a schema that will end up like this:

There are a couple of approaches you can use to write these tests and which you use will depending on your preference and circumstances.

The Simple Approach

The first of these, which I think of as the simple approach is to simply test the tables in situ; i.e against the real tables without any mocking or use of fake objects.

In this test, we generate an ID that we know does not exist in the [Severity] table – either because the table is empty or because we take an existing value and increment by 1 to ensure it isn’t valid.  In this test we’re only interested in testing the foreign key that references [Severity] so we have to use a couple of Test Data Builder (TDB) procedures to create stub entries in Task and Module. You can read more about using the TDB pattern here. We don’t need to set up a parent row in [Exception] as [Journal].[ExceptionId] is nullable.  Without those stub entries, we can’t guarantee that this test won’t fail on one of the foreign keys that reference Task or Module – which are the subject of separate unit tests.

We then insert a row to [Journal] where, with the exception of the missing Severity ID, we know all the column values are valid. The simplest way to test this is to do it within a TRY…CATCH… block and if the FOREIGN KEY exists as expected, we should get error number 547.  Then we just use tSQLt.AssertEquals to compare the expected error number with the actual error.  If we don’t get an error at all, or we get a different error number, the test will fail.  If you want to be really pedantic about this, you could also test for the presence of the foreign key name in the error message.

CREATE PROCEDURE [TestJournalTable].[Test_Journal_allows_only_referenced_SeverityId]
AS
BEGIN
    DECLARE @expectedError int; SET @expectedError = 547; -- FOREIGN KEY constraint violation
    DECLARE @actualError int; SET @actualError = 0;

    DECLARE @MissingSeverityId int; SET @MissingSeverityId = COALESCE((SELECT MAX(SeverityId) FROM [log4].[Severity]) + 1, 666);

    --! Set up the required reference data
    DECLARE @TaskId int; EXEC [TestDataBuilder].[TaskBuilder] @TaskId= @TaskId OUT;
    DECLARE @ModuleId int; EXEC [TestDataBuilder].[ModuleBuilder] @ModuleId = @ModuleId OUT;

    BEGIN TRY
        INSERT [log4].[Journal]
        (
          [UtcDate]
        , [SystemDate]
        , [TaskId]
        , [ModuleId]
        , [StepInFunction]
        , [MessageText]
        , [SeverityId]
        , [ExceptionId]
        , [SessionId]
        , [ServerName]
        , [DatabaseName]
        , [HostName]
        , [ProgramName]
        , [NTDomain]
        , [NTUsername]
        , [LoginName]
        , [OriginalLoginName]
        , [SessionLoginTime]
        )
        VALUES
        (
          '20110101'
        , '20110101'
        , @TaskId
        , @ModuleId
        , ''
        , ''
        , @MissingSeverityId
        , NULL
        , 1
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , NULL
        )
        ;

    END TRY
    BEGIN CATCH
        SET @actualError = ERROR_NUMBER();
    END CATCH;

    EXEC tSQLt.AssertEquals @expectedError, @actualError, '[log4].[Journal] table should not allow unreferenced SeverityId';
END

We run this test before and after creating the [FK_Journal_Severity] foreign key and it fails the first time then passes once the constraint is in place.

The Mocking Approach

The alternative method is to use tSQLt’s [FakeTable] to mock the tables under test in an attempt to reduce the need for setup data and isolate this test from any other tests and from the database itself.  We start by creating a fake copy of [Journal] then use a new (for this series) method called tSQLt.ApplyConstraint to add a copy of the foreign key we want to test to the mock [Journal] table.  [ApplyConstraint] can also be used to mock CHECK constraints but, at the time or writing, not primary or unique keys.  I believe that this functionality is planned for a future release.

As in the previous example, in this test, we generate a missing Module ID based on what, if anything, already exists in the [Module] table. Because we’ve faked the [Journal] table and the foreign key to Module is now the only referential key, there is no need to set up any other reference data in Task or Severity (the other mandatory foreign keys).

We then insert a collection of values in to [Journal] that, with the exception of the missing Severity ID, we know are valid.  Like the simple example earlier, we do the insert within a TRY.. CATCH… construct and check that the error number is what we expected (547).

CREATE PROCEDURE [TestJournalTable].[Test_Journal_allows_only_referenced_ModuleId]
AS
BEGIN
    EXEC tSQLt.FakeTable 'log4.Journal';
    EXEC tSQLt.ApplyConstraint 'log4.Journal', 'FK_Journal_Module';

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

    DECLARE @MissingModuleId int; SET @MissingModuleId = COALESCE((SELECT MAX(ModuleId) FROM [log4].[Module]) + 1, 666);

    BEGIN TRY
        INSERT [log4].[Journal]
        (
          [JournalId]
        , [UtcDate]
        , [SystemDate]
        , [TaskId]
        , [ModuleId]
        , [StepInFunction]
        , [MessageText]
        , [SeverityId]
        , [ExceptionId]
        , [SessionId]
        , [ServerName]
        , [DatabaseName]
        , [HostName]
        , [ProgramName]
        , [NTDomain]
        , [NTUsername]
        , [LoginName]
        , [OriginalLoginName]
        , [SessionLoginTime]
        )
        VALUES
        (
          999
        , '20110101'
        , '20110101'
        , 1
        , @MissingModuleId
        , ''
        , ''
        , 1
        , NULL
        , 1
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , ''
        , NULL
        )
        ;

    END TRY
    BEGIN CATCH
        SET @actualError = ERROR_NUMBER();
    END CATCH;

    EXEC tSQLt.AssertEquals @expectedError, @actualError, '[log4].[Journal] table should not allow unreferenced ModuleId';
END

This test too passes once the [FK_Journal_Module] foreign key is added to the [Journal] table.

Comparing the Two

The simple approach requires a little more setup, although in this example and utilising the TDB pattern it’s not really any more code than the mocking approach. Where you do need to be careful when using the simple approach is if there is already any data in any of the tables referenced by the test, or if breaks in the sequence of IDENTITY values will cause other problems.  If you weren’t already aware, an IDENTITY value is only used once, if the INSERT transaction that generates a new ID is rolled back, that particular ID value is not reused – leaving breaks in the sequence.

So this means that whilst we are testing the actual foreign key, the test isn’t truly isolated from the database and we are breaking one of the cardinal rules of unit testing – that the database should be left in exactly the same state as before the test.

The mocking approach isolates the test more effectively, although you can’t mock the parent table as tSQLt currently does not support applying a specific primary or unique keys to a fake table (but see update below).  although as of v1.0.4413.31717 of tSQLt released on 31-Jan2012, [FakeTable] can now carry the IDENTITY property across to the fake table.  This means that the test is not truly isolated from the database although in this particular example, we’re not making changes to data in real tables although when we start testing a foreign key’s ON DELETE/UPDATE actions we may have no choice but to alter data in real tables.

However, mocking the [Journal] table does mean we can get away with no other changes and it does simplify adding data to [Journal] for the purposes of this test.  Also, we are not testing the actual foreign key, instead we are testing a copy of it on the fake table and depending on tSQLt to ensure that the copy is identical to the original. In my experience this has proved to be a safe assumption.

UPDATE: It was only recently in a discussion with Dennis Lloyd Jr (blog | twitter), one of the authors of tSQLt, that I learnt of an undocumented feature in [ApplyConstraint].  When you use this procedure to to add a foreign key to a faked table, tSQLt will ensure that there is a corresponding unique index on the table being referenced. This means that, in the code snippit below, although we can’t apply an existing primary or unique key on [Severity], under the hood, a unique index is added to the parent table so that the specified foreign key can be added to the fake [Journal] table.  Although this is not documented, it makes sense when you think about mocking parent and child tables so is definitely worth remembering.

EXEC tSQLt.FakeTable 'log4.Severity';
EXEC tSQLt.FakeTable 'log4.Journal';
EXEC tSQLt.ApplyConstraint 'log4.Journal', 'FK_Journal_Severity';

Which is Best?

If you always run your tests against a completely fresh build of the database and are not affected by breaks in the sequence of auto-incrementing columns, the simple approach is fine.  Overall, my personal preference right now is for the mocking approach because it offers the most isolation from the database and in more complex tests, serves to minimise the amount of setup required for each test.

In this post, we looked at two approaches to writing simple unit tests for a foreign key, in the next post, Part 10, we will explore further tests we might want to implement for foreign keys – particular looking at cascading deletes and updates (or not cascading as the case may be).

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.

Comments on this entry are closed.

Previous post:

Next post: