How to add NOT NULL constraint to a column using a migration script (4216108)

Applies to
ApexSQL Source Control

Summary
This article describes specific use case when a NOT NULL constraint needs to be added to a column that does not have a DEFAULT constraint specified. Without a DEFAULT constraint, applying such a change on another database will fail. In order to avoid this scenario, using the migration script, a default value will be added.

The first part of the article describes the situation when there are no NULL values in the appropriate column. The second part describes the problem when NULL values exist on both sides, locally, and on another local copy of a database, where the specific change should be applied.

As a starting point, it is assumed that a database is already linked to the repository by all developers using dedicated development model, and that is synchronized with the repository.

A simple table will be used in this article as an example. The CREATE script for the table in question is as follows:

CREATE TABLE [Production].[Categories] (
	[categoryid] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY
	,[categoryname] [nvarchar](15) NULL
	,[description] [nvarchar](200) NULL
	)
GO

The goal is to add a NOT NULL constraint for the description column, and to ensure that the rest of the team that are working on their own copies of a database have this change applied properly.

Description

Let’s inspect the case when the description column does not contain NULL values.

The following script is used to add a NOT NULL constraint to a description column:

ALTER TABLE [Production].[Categories] ADD CONSTRAINT [CK_description_NOTNULL] 
CHECK ([description] IS NOT NULL)
GO

Such a statement will be executed successfully in case there is no NULL value in the description column of the Categories table:

If the change is committed to the repository, it will appear in the Action center tab on other user’s machine who is working on a copy of a database linked to the same repository:

Such a change can be successfully applied against a database upon selecting in the Action center tab and clicking the Apply button. The result is a NOT NULL constraint successfully created in a target database:

Obviously, there is no need to use migration script in this case, since when there is no NULL value presented, the NOT NULL constraint can be added locally and applied on other copies of a database.

Quick tip: A migration script is not needed in case there is no data at all in a column where the NOT NULL constraint is added

Another scenario is when the description column contains at least one NULL value.

If the description column of the Categories table contains NULL values, the above change (adding the NOT NULL constraint) cannot be applied. For instance, after adding a few rows of data in the description column and querying the Categories table, the result is as follows:

As shown above, the description column contains NULL values. The following SQL script is executed to add a NOT NULL constraint:

ALTER TABLE [Production].[Categories] ADD CONSTRAINT [CK_description_NOTNULL] 
CHECK ([description] IS NOT NULL)
GO

The following error is displayed:

Msg 547, Level 16, State 0, Line 8
The ALTER TABLE statement conflicted with the CHECK constraint "CK_description_NOTNULL". The conflict occurred in database "<database_name>", table "Production.Categories", column 'description'.

In order to add NOT null constraint, there should be no NULL values in the column for which the constraint is going to be added. To fix this, the following UPDATE statement will change the NULL values and set them to <Unspecified> in the description column:

UPDATE [Production].[Categories]
SET [description] = '<Unspecified>'
WHERE [description] IS NULL
GO

The result of querying the Categories table again, shows that there are no more NULL values in the description column. This means that the NOT NULL constraint can be added. If such a change is committed to the repository, it will be shown in the Action center tab, for other team members working on a copy of the same database locally:

On any attempt to apply such a change without the migration script, the following dialog appears:

This change cannot be applied on any other copy of a database in question without adding a migration script that will run prior to this change setting the NULL values to <Unspecified>.

Adding a migration script

Once the NOT NULL constraint is added on local machine it will appear in the Action center tab. Before committing it to the repository, right-click on it, and from the context menu select the Add migration script option:

This initiates the new query for the migration script to be specified:

Before the migration script part, it is ensured that the migration script is executed if the appropriate column exists (in this case the description column) in a database where this change is going to be applied. Upon clicking the Save button, the migration script will be saved, and shown in the Action center upon refreshing. To commit this, both items must be selected:

Quick tip: Once the migration script is added, it must be committed along with the change. The same goes when applying the change against other database.

Other developers will see the exact same change with the Get action (blue arrow) set by default:

Clicking the Apply button initiates the Apply changes form where the exact script, that will be executed, will be shown under the Scripts tab:

As marked above, prior to adding the NOT NULL constraint, the guard clause will check that the description column exists, the migration script will set all NULL values in the description column to <Unspecified>, and at the end the NOT NULL constraint is added.

As a result, the change made initially is applied successfully:

Q: Will the NOT NULL constraint be applied in case a migration script is committed along with the change and there is no NULL value in the destination column?

A: Yes, NOT NULL constraint will be properly applied. The migration script ensures that if there are NULL values in the target column, those will be changed to <Unspecified>

Q: Will the migration script be executed If the guard clause inside the migration script does not exist?

A: Yes, if the requirements are met. In this particular case, the description column must exist on the destination side.