Applies to: xSQL Schema Compare v7.0.0 – v 9.0.0, xSQL Data
Compare v7.0.0 – v9.0.0
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.
Posts
0 comments:
Post a Comment