tsql - Default constraint not being enforced -

given following table definition:

create table controlledsubstances.nationaldrugcode (     nationaldrugcodeid int not null     ,nationaldrugcode varchar(20) not null     ,product varchar(100)     ,ingredient varchar(500)     ,classid varchar(50)     ,class varchar(50)     ,drugenforcementagencyclassid varchar(50)     ,drugenforcementagencyclass varchar(50)     ,genericdrug varchar(50)     ,form varchar(50)     ,drug varchar(50)     ,strengthperunit numeric(6,2)     ,unitofmeasure varchar(50)     ,conversionfactor numeric(4,2)     ,longorshortacting varchar(50)     ,ispreventionforstates bit not null ) ;  alter table controlledsubstances.nationaldrugcode     add constraint pk_controlledsubstances_nationaldrugcode primary key (nationaldrugcodeid)         ,constraint df_controlledsubstances_nationaldrugcode_ispreventionforstates default 0 ispreventionforstates ;  create unique index uq_controlledsubstances_nationaldrugcode_nationaldrugcode on controlledsubstances.nationaldrugcode (nationaldrugcode); 

why receiving error on insert column defined not null , created default constraint of 0? know can handle logic in insert statement not pass in null values, use logic in multiple tables , have never gotten error before. error receive is:

 cannot insert value null column 'ispreventionforstates', table 'staging.controlledsubstances.nationaldrugcode'; column not allow nulls. insert fails. 

this happen if explicitly provide null value. default constraint kicks in when don't supply value @ all, or when use default keyword:

for example, if nationaldrugcodeid , ispreventionforstates 2 columns in table (for illustration), fail:

insert nationaldrugcode(nationaldrugcodeid, ispreventionforstates) values (5, null); 

but either of these work:

insert nationaldrugcode(nationaldrugcodeid) values (5);  insert nationaldrugcode(nationaldrugcodeid, ispreventionforstates) values (5, default); 

in edge case need all columns have default values inserted, can use:

insert nationaldrugcode default values;