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