为何tSQLt默认不支持非空列？

First, let's clarify the simple modification you've identified—it's indeed a straightforward tweak to the [tSQLt].[Private_CreateFakeOfTable] procedure:

-- Original logic
CASE WHEN cc.IsComputedColumn = 1 OR id.IsIdentityColumn = 1

-- Modified logic
CASE WHEN cc.IsComputedColumn = 1 OR id.IsIdentityColumn = 1 or c.is_nullable = 0

This change tells tSQLt to handle non-nullable columns the same way it treats computed or identity columns. Now, to address your question about why this isn't the default behavior:

• Prioritizing Test Flexibility: The core purpose of FakeTable is to create a lightweight, isolated copy of a table for testing. By default, tSQLt relaxes non-null constraints so you can insert only the specific rows/columns your test needs, without having to populate every non-null field (which might require setting up irrelevant default values or dummy data). This keeps tests focused and cuts down on boilerplate code.

• Avoiding Unnecessary Test Dependencies: If non-null constraints were enforced by default, tests would become tightly coupled to the schema's non-null rules. For example, if you're testing a stored procedure that only interacts with 2 out of 5 non-null columns, you shouldn't have to waste time populating the other 3 just to make the fake table work.

• Historical Design Tradeoffs: tSQLt's design has always leaned toward making testing as frictionless as possible. The decision to skip enforcing non-null constraints by default was likely a deliberate choice to keep the tool accessible for most common testing scenarios, with the expectation that power users could modify the internals if needed.

Your Proposed Approach: Safe & Practical #

Modifying this code directly is low-risk for your own use case, but as you mentioned, wrapping it into an optional parameter is even better. For example, you could add a @EnforceNotNullConstraints BIT = 0 parameter to Private_CreateFakeOfTable, then adjust the CASE statement to:

CASE WHEN cc.IsComputedColumn = 1 OR id.IsIdentityColumn = 1 OR (@EnforceNotNullConstraints = 1 AND c.is_nullable = 0)

This way, you retain the default behavior for existing tests, but can enable non-null enforcement when you need it for specific test cases.

内容的提问来源于stack exchange，提问作者bielawski