Problem with 'default' constraint

Question

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.

Actually I think I might have misread the question Can you confirm whether the issue is that you were expecting the default to apply to pre-existing rows or that it doesn't apply to newly inserted rows? If the latter can you show us the insert statement you are using? Are you explicitly inserting NULL for example? — Martin Smith
– Martin Smith, Commented Mar 1, 2011 at 12:25
see, when I m not inserting any value into 'type' column, then by the 'default' constraint, it should show default value. when i fetch all rows, 'type' shows no value. for the next, when i insert NULL(without quotation) in 'type' column then select query shows that 'NULL' word. i want the output that, when i m not inserting anything into 'type' column, it should show default type as 'baby cot'. — Pooja
– Pooja, Commented Mar 1, 2011 at 16:49
Why are you inserting NULL if you want it to have the default? You need to either do insert into interiors(id,name,price) values (1,'foo',100) OR insert into interiors(id,name,type,price) values (1,'foo',DEFAULT,100) inserting NULL isn't treated any differently then inserting any other explicit value. — Martin Smith
– Martin Smith, Commented Mar 1, 2011 at 17:01

Martin Smith · Accepted Answer · 2011-03-01 12:18:29Z

1

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

answered Mar 1, 2011 at 12:18

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pooja Over a year ago

I am not understanding what you want to say exactly. Will you please explain more?

Mikael Eriksson · Accepted Answer · 2011-03-01 13:06:49Z

1

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

edited Mar 1, 2011 at 13:06

answered Mar 1, 2011 at 12:37

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

Martin Smith Over a year ago

+1. You could also show insert into @T (ID, Name) values (4,default)

Collectives™ on Stack Overflow

Problem with 'default' constraint

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related