tSQLt apply NOT NULL constraint

How do I apply the NOT NULL constraint back onto a column of a table in tSQLt?

CREATE OR ALTER PROCEDURE [test abc].[test abc1]
AS
BEGIN
    -- Arrange
    EXEC tSQLt.FakeTable 'dbo', 'table1';
    -- Here the NOT NULL constraint should be put back on table1 column col1
    EXEC [tSQLt].[ExpectNoException]
    -- Act + Assert
    INSERT INTO .....
END
GO

Answer

The OP is asking specifically how to do this in the context of a tSQLt unit test.

For those that don’t know, tSQLt.FakeTable temporarily replaces a real table with a mock which has the same schema, table and column names plus all of the same data types but none of the constraints. So all columns allow NULL, and all keys, defaults, check constraints and even IDENTITY property are removed for the duration of the test. This can be useful when writing unit tests against a single table that has many dependencies – it minimises the set up required for each test. At the end of every test, the transaction within which that test has run is rolled back and the original table structure is returned.

Now to the actual question, whilst tSQLt.Fake table does allow certain features of the original table to be retained in the mock including auto-increment, computed columns and default constraints, nullability is not one of those features. Although if @SebastianMeine or @dennislloydjr is reading this, such a feature might be a useful value-add.

I don’t believe there is no automatic way to apply NOT NULL to columns in a faked table, however it does raise a question about your use case. Whilst FakeTable is incredibly useful in many test scenarios, there are cases where you would want to run one or more tests against the real table. The obvious example here is a stored procedure designed to INSERT a new row to that table. If a new NOT NULL column is added to a table after the insert sproc was written, the procedure will then fail because it does not insert a value into the new mandatory column. You would definitely want to pick that up in testing and having at least one test of that stored procedure against the real table that would detect that bug before you get to production.

If your use case requires that only a specific column be set back to NOT NULL, then you can do it manually UPDATE myTable ALTER COLUMN myColumn <datatype> NOT NULL after calling tSQLt.FakeTable although the danger with that approach is if the data type or nullability of that column changes in future, you will have no way of automatically changing the column definition in your test – which makes for a less reliable test.

I am sorry there is no easy answer to your question.