Synchronizing NOT NULL Columns

Description: Using

xSQL Schema Compare and xSQL Data Compare to keep the tables of two databases

in sync is a no-brainer, and, almost always, the default synchronization options

will do the trick. However, there are a few special cases in which knowing how

to correctly manipulate the comparison and synchronization options is a must to

complete the synchronization process correctly. One of this cases is the

synchronization of NOT NULL Columns.

Let’s say there are two tables in two different databases

that need to be synchronized. Both of these tables have data and one of them has one or more columns

with a NOT NULL constraint and no

default value. In this case, synchronization of these table’s schemas using

the default options is not possible.

Reason: This is

because when you generate the synchronization script xSQL Schema Compare will create

the following statement:

Notice the NOT NULL without a default value specification in

the ALTER TABLE statement. Logically this is correct because one of the tables

has the ‘TestColumnNotNull’ with a NOT NULL constraint and no default value, so

it will try to create the same column on the other table. But, because these

tables both have data, adding a column in one of them would mean that the

values for this column would be NULL. Since the column has a NOT NULL constraint

SQL Server will not allow the addition of this column.

Workaround: The

solution to this is to force xSQL Schema Compare to script the new columns as

NULL by checking the ‘Script new columns as NULL’ in the comparison options

dialog (picture below):

This time, when the synchronization script is generated it

will not add the NOT NULL constraint, and SQL Server will allow the schema

synchronization. After this, the column on the target database can be manually updated with valid data, or the data synchronization with xSQL Data Compare can be performed, which will

fill the newly added column with values. To add the NOT NULL constraint, all

that needs to be done is to uncheck the ‘Script new columns as NULL’ and

perform the schema comparison and synchronization. This time SQL Server will

have no problem with adding a NOT NULL constraint in the new column because it

does not have any NULL values.

And there you go, the tables are synchronized.

CodeProject