CODEWITHSUNDEEP
sqlsql-server
Pooja
Pooja

Reputation: 33

Problem with 'default' constraint

I created the following table

create table interiors
(id integer, name varchar(20), type varchar(20) default 'baby cot', 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.

Upvotes: 0

Views: 143

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

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

Upvotes: 1

Related Questions