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 

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

EXEC SP_HELP STUDENT_DETAILS

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.

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

SELECT * FROM SYS.default_constraints WHERE  parent_object_id=912162445 

Here we get the constraints name that we have created.

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

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

 alter table STUDENT_DETAILS drop column IS_REGULAR 

That's all. Thank you for visiting.