SQL: Naming DEFAULT Constraints

I recently wrote about how SQL Server related tooling has been pushing us to use constraints like primary keys, check constraints, and unique constraints without naming them ourselves. But a similar issue applies to DEFAULT constraints.

With DEFAULT constraints, the issues are slightly different.

The first issue with them is the same as with the other constraints. They can cause havoc with deployment and schema-comparison tooling unless you find and use options to ignore any system-generated names.

The second issue is that you often have to use these names, even though you don’t know what they are.

SQL Server DEFAULT Constraints

In SQL Server, default values for columns are provided by DEFAULT constraints.

Years back, SQL Server also had a concept of a default as a type of database object. The idea there was that you could have a single default value that was applied to multiple columns. There was also a concept of a rule. Neither of these should still be in use now.

But of course, default values for columns are still alive and well. And again, the tooling seems to like you to just use system-generated names.

The problem

In theory it doesn’t matter what you call the default constraint however, if you want to drop a column, the first thing you need to do is to drop the constraint. That’s a problem when you don’t know what it’s called.

Imagine you are writing a deployment script to drop a column. That’s going to be far more complex than it should be.

Avoiding the issue

Database engines like PostgreSQL don’t have this issue as they don’t let you name column default constraints. The consider the default value to just be an attribute of a column, like any other attribute such as data type.

That means that when you drop a column, they constraint is automatically gone as well. That makes far more sense than the SQL Server implementation.

It’d be nice if the SQL Server team did the same thing but assuming they won’t do so any time soon, the best fix would be if dropping a column in T-SQL had an option to automatically drop any default on the column, no matter what name it had.

The workaround

Given that SQL Server doesn’t have a good option here, at least things are easier if I’ve consistently named them. I won’t have to write a query to find the constraint name before I drop it.

And naming is easy because I can’t have two defaults on a column. The combination of schema name, table name, and column name is unique. So I just need to use all those in the name like so:

    ClientName nvarchar(100) NOT NULL,
    SpecialRate decimal(18, 0)
        CONSTRAINT DF_dbo_Clients_SpecialRate DEFAULT(0),

That means that dropping a column becomes easier:

ALTER TABLE dbo.Clients DROP CONSTRAINT IF EXISTS DF_dbo_Clients_SpecialRate;
ALTER TABLE dbo.Clients DROP COLUMN IF EXISTS SpecialRate;

The IF EXISTS options make it easy now to write this as idempotent code. But if you are working with a version earlier than 2016 that doesn’t support the IF EXISTS options, you could query the sys.default_constraints view first.

Again, I’d like to hear your thoughts on this. How do you name your DEFAULT constraints?

2026-06-15