Adding and removing the default constraint on a table

Adding and removing the default constraint on a table

Default value is something like to adding the values into specific columns and if not specify then it will automatically insert some value it means that the column should not fill with the null values.

Adding the default constraints,

in my case i have an table MergeMe

CREATE TABLE [dbo].[MergeMe](
[id] [int] IDENTITY(1,1) NOT NULL,
[Sname] [varchar](100) NULL,
[salary] [money] NULL,
[dtdate] [datetime] NOT NULL
)

to add default constraint ,

alter table MergeMe add default getdate() for dtdate

insert into MergeMe(Sname,salary) values(‘vikrant',10000)

it will insert records and in dtdate it will insert the getdate() which we have set as default.

now if we try to delete the column it will gives us an error,

after running this query 

alter table MergeMe drop column dtdate

give error,

Msg 5074, Level 16, State 1, Line 2
The object ‘DF__MergeMe__dtdate__689D8392' is dependent on column ‘dtdate'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE DROP COLUMN dtdate failed because one or more objects access this column.

to find out the dependencies, run the following query ,

 

select 
col.name, 
col.id, 
OBJECTPROPERTY(col.id, N'dtdate') as is_ID, 
dobj.name as def_name
from syscolumns col 
left outer join sysobjects dobj 
on dobj.id = col.cdefault and dobj.xtype = ‘D' 
where col.id = object_id(N'dbo.mergeme') 
and dobj.name is not null

name id                 is_ID     def_name
dtdate 1739153241 NULL    DF__MergeMe__dtdate__689D8392

now we got the dependent name from the error or from the above query,

drop the constraint using following query,

alter table MergeMe drop constraint DF__MergeMe__dtdate__689D8392

after this now we can drop the column,

alter table MergeMe drop column dtdate
Next Recommended Reading SQL Keyword - ADD CONSTRAINT