How To Delete A Column With Default Constraints In SQL Server

This article let us know how to delete a column that has a default constraint.

Let us create a  student table with some attributes as below.

  1. STUDENT_ID
  2. STUDENT_NAME
  3. EMAIL
  4. MOBILE
  5. IS_REGULAR
CREATE TABLE STUDENT_DETAILS (
STUDENT_ID	   INT   PRIMARY KEY 
,STUDENT_NAME  VARCHAR(32) 
,EMAIL		   VARCHAR(32)
,MOBILE		   CHAR(10)
,IS_REGULAR    BIT  DEFAULT(0)
)
select * from STUDENT_DETAILS 

How To Delete A Column With Default Constraints In SQL Server

Now we drop the IS_REGUAL column from the table, which has a default constraint as below.

EXEC SP_HELP STUDENT_DETAILS

Delete A Column With Default Constraints In SQL Server

Now if we drop the column by using the below query 

alter table STUDENT_DETAILS drop column IS_REGULAR 

Then we will face the below error.

Delete A Column With Default Constraints In SQL Server

Now we have to first drop the constraints then we need to drop the column by using the below query.

SELECT object_id FROM SYS.COLUMNS WHERE NAME='IS_REGULAR'

Here we get the object Id

Delete A Column With Default Constraints In SQL Server


SELECT * FROM SYS.default_constraints WHERE  parent_object_id=912162445 

Here we get the constraints name that we have created.

Delete A Column With Default Constraints In SQL Server

Now we are able to drop the constraints by using the above name.

alter table STUDENT_DETAILS drop constraint DF__STUDENT_D__IS_RE__3846C6FF

Delete A Column With Default Constraints In SQL Server

Now we are able to drop the column by using the below query.


 alter table STUDENT_DETAILS drop column IS_REGULAR 

Delete A Column With Default Constraints In SQL Server

That's all. Thank you for visiting.

Recommended Ebook

Functions in SQL Server: Practical Guide

Download Now!
Similar Articles