Problem with 'default' constraint

I created the following table

create table interiors
(id integer, name varchar(20), type varchar(20) default 'baby c开发者_运维知识库ot', price integer)

When I don't insert any value of 'type', by the definition of table, it should take the default value. But when I fetch all rows by 'select' query, it doesn't show default value.

Can anyone tell me that what is the problem?

Thanks & regards,

Pooja.

Defaults apply to data that is inserted when the default is active not to select statements against the table.

Not sure what the problem is for you but here is some hints for you what is going on.

This T-SQL

declare @T table (ID int, Name varchar(10) default 'DEF')

insert into @T (ID, Name) values (1, 'VAL')
insert into @T (ID, Name) values (2, null)
insert into @T (ID) values (3)
insert into @T (ID, Name) values (4, default)

select ID, Name
from @T

Have this result

ID          Name
----------- ----------
1           VAL
2           NULL
3           DEF
4           DEF

Default constraint is not used when you specify a value for a column as in row 1 and 2 but it is used when you omit the column from the insert or you specify default. It is also not used when you do a select.

If you want a default value instead of null on select you can use coalesce.

select ID, coalesce(Name, 'DEF') as Name
from @T

Result

ID          Name
----------- ----------
1           VAL
2           DEF
3           DEF
4           DEF