Renaming Default Constraints

Bill Fellows has a script to standardize default constraint names:

This week I’m dealing with synchronizing tables between environments and it seems that regardless of what tool I’m using for schema compare, it still gets hung up on the differences in default names for constraints. Rather than fight that battle, I figured it’d greatly simplify my life to systematically rename all my constraints to non default names. The naming convention I went with is DF__schema name_table name_column name. I know that my schemas/tables/columns don’t have spaces or “weird” characters in them so this works for me. Use this as your own risk and if you are using pre-2012 the CONCAT call will need to be adjusted to classic string concatenation, a.k.a. +

Click through for the script and be sure to check out Robert’s comment.  You can also revise this script to “rename” (i.e., drop and re-create) foreign key or check constraints, though those might take a while depending upon how much data you’ve got.

Related Posts

Missing Foreign Keys—A Cultural Problem

Martin Catherall tells a spooky Halloween story: By large databases I’m roughly meaning databases with several hundred tables, and I usually see a lot of these tables with several hundred GB’s of data in them. When I generally ask about the reason for no foreign key, I’m told they add  overhead they give no benefit […]

Read More

Natural Keys?

Steve Jones wonders if we should give up on natural primary key constraints: One of the things I think is important in modeling your particular entity is including a primary key (PK). In my DevOps talk I stress this, as I’d rather most attendees come away thinking a PK is important as their first takeaway […]

Read More


July 2017
« Jun Aug »