We have on the server a database and we want to replicate a database on another server. we've set a transactional replication, but we have issues with the default constraints on the tables:

enter image description here

On the left is schema of the table from a backup from the original server and on the right is the schema from the replicated backup. Is there some setup on the replication so that the constraints remain the same on both databases? I've used Sql Schema Compare from visual Studio 2013 to compare the databases.

  • Brilliant, the beauty of unnamed constraints :). So just use your own constraint names on the publisher and you'll have them the same on the subscriber. – Marian Jul 14 '15 at 14:25
  • yeah, I wanted to avoid this because in our existing database, there are more than 200 tables with constraints without names... – Buda Gavril Jul 15 '15 at 5:15

The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool.

The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name explicitly, SQL Server picks one for you, because ultimately a constraint must have a name. The names on the right are the ones chosen by SQL Server.

So, the two sets of constraints most likely are the same in both databases. It is only the DDL statements that differ, because yours does not assign constraint names.

You can assign your own names if you use the syntax shown in the query on the right. That is, instead of declaring a default value like this:

DEFAULT (default_value)

use the following syntax:

CONSTRAINT constraint_name DEFAULT (default_value)

That way the names will always be predictable and, when comparing the schemas, you will not run into this issue again.

The same applies to all types of constraints in SQL Server (defaults, primary keys, foreign keys, check constraints).

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.