Drop DEFAULT Constraint in SQL Server

In my previous article, I explained about how to assign a DEFAULT value to a column.
In this section I will explain about how to drop/remove DEFAULT value from a column.

Let’s take a step back little bit and see, what is the syntax that was used to create DEFAULT value?

ALTER TABLE DEPTADD CATEGORY VARCHAR(3) NOT NULL DEFAULT ('IT')

If you look at the syntax, I didn’t mention the name of the constraint.

But the thumb rule in SQL language is you need to have a name to drop anything!!!!!

So to drop this constraint we need to find out the name of the default constraint that was given by SQL Server.

NOTE: If you don’t provide any name to constraints, SQL Server will automatically assign one for you.

Let’s find out the name of the constraint. All the default constraints are stored in SYS.DEFAULT_CONSTRAINTS table.

Below, I listed out all the important columns from SYS.DEFAULT_CONSTRAINTS table.

SELECT NAME,OBJECT_ID,PARENT_OBJECT_ID,TYPE,
TYPE_DESC,PARENT_COLUMN_ID,DEFINITION
FROM SYS.DEFAULT_CONSTRAINTS



In my database I have only one DEFAULT constraint that’s the reason I have only one record. If you have multiple rows then how would you identify the name of the constraint?? In that case you need to resolve the PARENT_OBJECT_ID, PARENT_COLUMN_ID columns.

NOTE: When ever you create an object in SQL Server, it will automatically assign one unique number and stores that information in OBJECT_ID.

EX: SELECT NAME,OBJECT_NAME(OBJECT_ID) FROM SYS.DEFAULT_CONSTRAINTS
If you want to find out the name of the object then pass OBJECT_ID to OBJECT_NAME function.

Let’s come back to our original task. So after resolving the names using IDs the query will be

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
SELECT @DEF_CONS_NAME




Now the next step is to construct the dynamic query to remove.

DECLARE @DEF_CONS_NAME VARCHAR(100);
SELECT @DEF_CONS_NAME = NAME
FROM SYS.DEFAULT_CONSTRAINTS
WHERE OBJECT_NAME(PARENT_OBJECT_ID) = 'DEPT'
AND upper(COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID)) = 'CATEGORY'
exec('alter table dbo.DEPT drop constraint ' + @DEF_CONS_NAME)

That’s it. It’s very simple isn’t it?

If you closely observe the above query I used OBJECT_NAME, COL_NAME, UPPER functions. I will explain about these in my next article.