Force Default value on NOT NULL column when NULL is passed? - Frank Osterberg

Hi,

i have a table which has several "NOT NULL" columns and a default value for
each.

In MySQL the default value is used for 'not null' columns when someone
executes an insert statement without specifying that column or when the
passed value is NULL or DEFAULT.

When i try to do the same in SQL Server 2005 i always just get the error
'field cannot be null' error!
Is ther something wrong or does it just not work that way in SQL Server?
How can i make this work like that?

I though maybe a trigger, but i want it to be as fast as possible and i am
not even sure how i would write that trigger.. is it possible to change
insert values passed to a trigger BEFORE they get inserted?

Any suggestion would be greatly appriciated!

regards, frank

Force Default value on NOT NULL column when NULL is passed? - Tibor Karaszi

If you specify a NULL for the column then the engine will complain that a NULL is not allowed. That
NULL will not be "automagically" converted to your default value. MySQL apparently do not adhere to
the ANSI SQL behavior. You could have an INSTEAD OF trigger which "intercepts" the INSERT and
re-generates an insert the way you like it. I prefer to have the application behave correctly,
though.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Force Default value on NOT NULL column when NULL is passed? - Gert-Jan Strik

Frank,

First of all, SQL Server would throw an error if you explicitely insert
a NULL value into a column that is not nullable, although it would be a
different error than the one you mention.

However, omitting the column when inserting, or inserting DEFAULT will
*not* lead to an error, but will simply insert the default.

For example, the following batch will not throw an error:

create table #t(id int,col2 int not null default 0)
insert into #t (id) values (1)
insert into #t (id,col2) values (2,default)
drop table #t

--
Gert-Jan