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.
| Author |
Topic |
|
Tim
Starting Member
392 Posts |
 Posted - 2002-05-22 : 20:00:02
|
| Check this out in SQL 2000If you set a bit column 'Allow Nulls' = False and include a default value (say 0) I would have thought that whenever a null value came along, 0 would be entered in the column in the new record by default.What you get is error "Cannot insert the value NULL into column 'yakflag', table 'yaks.dbo.yak'; column does not allow nulls. UPDATE fails. The statement has been terminated."Alternatively, if you make the column 'Allow Nulls' = True and keep the default value setting of 0, the column remains NULL instead of the default value in the new record.Am I missing something? Is a trigger the only way to replace null values with a default in bit columns?... confused ... |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-05-22 : 22:15:22
|
| default values are only used when the column is not included in an insert statement. it has nothing to do with nulls. you could try <isnull(@data, 0)> if you need to... |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-22 : 22:22:58
|
Well, that's perfectly normal behavior.I think you're assuming that the constraints will do something to fill in the blanks, when in fact that's not what they are designed to do. Constraints PREVENT illegal data from being inserted into a table, that's all; they do NOT create legal data from nothing. The default WILL insert a legal value, AS LONG AS you don't provide an explicit value for that column, like null.In the example you provided, when the column was set as NOT NULL, and you tried to insert a null, the first thing that happens is that the constraint says "Whoa there, Null, you can't go in there" and it aborts the whole operation and throws an error. Once that happens, it's over, the default will not magically kick in to fix the problem. You were explicitly telling the table "I'm inserting a row, and I want to a null in this column":CREATE TABLE BitTest (col1 bit NULL DEFAULT (0) )INSERT INTO BitTest (col1) VALUES (Null)Instead of "I want to insert a row, I don't have a value for this column, just use the default":CREATE TABLE BitTest (col1 bit NULL DEFAULT (0) )INSERT INTO BitTest (col1) VALUES (DEFAULT)-- orINSERT INTO BitTest DEFAULT VALUESAnyway, to make a long boring discussion short, if you don't want nulls in that column, then declare it NOT NULL and put the default on it, and use the DEFAULT keyword when you don't have another value to insert. Edited by - robvolk on 05/22/2002 22:23:30 |
|
|
|
|
|
|
|
|
hmmmm bits and nulls