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