It is way simpler to drop a default constraint if you know its name. But in real life, we often create a column with a default value without giving it a custom name. It works well until we change our mind and want to change the default or even drop it. Then the real problem begins. How to drop default constraint without knowing its name in SQL Server?
Default constraint in SQL Server
Default constraint is a special type of constraint that does not limit allowed values, but assigns a specific value when one is not provided in the INSERT statement. For example, you can specify a default value when creating a table.
create table dbo.MY_TABLE (
COLUMN_A int,
COLUMN_B int not null default 2
);
It creates a table MY_TABLE with two columns: COLUMN_A and COLUMN_B. The second one is marked as not nullable, which means that it cannot store a null value. But thanks to the special clause (default 2), even if you don't provide a value for the COLUMN_B, like in the INSERT statement below, SQL Server will assume 2.
insert into dbo.MY_TABLE (COLUMN_A)
values (1);
Specifying a default value during a table creation is not the only option. It can be done anytime later as well with an ALTER TABLE syntax.
alter table dbo.MY_TABLE
add default 2 for COLUMN_B;
Any new rows inserted into MY_TABLE will not require providing a not null value for COLUMN_B. The database will use a default value 2.
But wait! Although many developers do that, I don't recommend using the above statements for default constraint creation. Every structural object in the database must have a unique name. Sometimes unique in the scope of the whole database, a different time in the scope of a table or another scope. But it must have a name. A default constraint like any other constraint is an object. If you don't provide a name for it, SQL Server will not protest, but silently generate a random unique one and will use it. It might be good that will not bother you to ask for a name when creating a default constraint. But on the other hand, you will end up with random names, if you are not aware of what is going on.
Instead of allowing random names, I strongly suggest specifying a proper name yourself. Then, you will always have consistent names across all environments if you use the same script to create them. A proper way to create a default constraint with a name is this:
create table dbo.MY_TABLE (
COLUMN_A int,
COLUMN_B int not null constraint DF_COLUMN_B_DEF default 2
);
alter table dbo.MY_TABLE
add constraint DF_COLUMN_B_DEF default 2 for COLUMN_B;
Now you know how to create a named default constraint. But you may wonder why it matters. Because it does! Most database engines require providing a constraint name when dropping it. MySQL is an exception, but that's a different story. Other engines like SQL Server have a dropping syntax like this:
alter table dbo.MY_TABLE
drop constraint DF_COLUMN_B_DEF;
You cannot blame Microsoft, the database engine must have a way to identify which constraint you want to drop. Using a unique name is almost a perfect solution. Almost, because you have to know its name first. And it is better to not be random.
Drop default constraint without knowing its name
On the other hand, who worries about dropping a constraint when creating it? Hopefully all of us, but let's be realistic. Sometimes we just forget. Then the same script is executed on all environments: development, test, production, and all others. The database engines generated different constraint names in each environment. Later, something changes in the requirements and the current default constraint must be dropped. How to create a statement that will drop it from all environments if each one has a different auto-generated name? It will not be a simple statement. You have to put more effort than this. You have to write a script.
A script that drops a default constraint without knowing its name is here:
declare @tableSchema nvarchar(255);
declare @tableName nvarchar(255);
declare @columnName nvarchar(255);
declare @name nvarchar(255);
-- update below lines
set @tableSchema = N'dbo';
set @tableName = N'MY_TABLE';
set @columnName = N'COLUMN_B';
select @name = OBJECT_NAME([default_object_id])
from SYS.COLUMNS;
where object_id = OBJECT_ID('['+ @tableSchema + '].[' + @tableName + ']') and name = 'columnName';
exec('alter table ['+ @tableSchema + '].[' + @tableName + '] DROP CONSTRAINT ' + @name);
GO
The first section is a declaration of four variables. Then, three lines with SET statements define a table and a column from which you want to drop a constraint. If you want to use it, update these values first. The next query checks the system table and uses system functions to check what constraint is assigned to the specified column and what is its name. The script ends with building a dynamic SQL statement that drops the constraint using the name found earlier.
As you can see, if constraint names are random, you have to put much more effort into dropping them. Dropping a default constraint without knowing its name is not impossible but it is still easier to create named constraints in the first place.
