null constraint with default value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

null constraint with default value

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-08-01 : 17:12:15

If a column allows null, and has a default value assigned, will a null value that is passed in for example, with an insert statement, result in a null value or the default value being inserted ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-01 : 17:18:43

If you pass in a value, then that value is used regardless if it is null or not. It's when you don't specify a value for that column that the default is used.

Here's an example:

--NULL value gets assigned to Column2
INSERT INTO Table1 (Column1, Column2)
VALUES 1, NULL

-- default value gets assigned to Column2
INSERT INTO Table1 (Column1)
VALUES 2

Tara Kizer
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-02 : 02:47:42

Well. Except that you need paranthesis when you use VALUES

--NULL value gets assigned to Column2
INSERT INTO Table1 (Column1, Column2)
VALUES (1, NULL)

-- default value gets assigned to Column2
INSERT INTO Table1 (Column1)
VALUES (2)

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-02 : 12:09:53

I'm sure my T-SQL didn't need to be corrected as the question wasn't how to write an INSERT statement.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-08-02 : 12:23:52

I reckon you'll do well if you ever take up politics Tara!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-02 : 12:44:06

I love debates!

Tara Kizer
http://weblogs.sqlteam.com/tarad/