Default Constraints & Not NULL
-
This may sound stupid, but I just discovered DEFAULT and NOT NULL are (in practice) mutually exclusive.
I'd never considered the issue until I was given the job of designing the physical model of our new data warehouse. I decided that all money amounts that came across as NULL were going to be forced into 0.00 so that any math done on those columns wouldn't require ISNULL() checks. In my design, I set the columns all NOT NULL and set the DEFAULT as 0.00, thinking that any NULL amount attempting to come over from the OLTP database would be forced to be 0.00 and no one would be able to use code on the back end to force a money value to be NULL. Sounds simple right?
Unfortunately for me, who spent 2 days designing and building my tables, it's not. NOT NULL evalutes before the DEFAULT evaluates, rejecting any attempt to stick in a NULL money and have it default to zero. So, if I want to force a value to be zero, I have to use ISNULL() in the Insert statement, which negates the value of having the DEFAULT constraint in the first place. And if I turn off NOT NULL so that the DEFAULT works, I allow developers and other DBAs to insert NULL values in all my money columns via T-SQL statements.
Have you run into this issue before? Which one of the above did you choose as the best method of doing things? Or, did you find a workaround?
I'd love hearing other people's thoughts on the issue. Thanks in advance!
-
If you want the default to be used you have to leave the column out of the insert. I have little experience in large system designed like this so I'll leave the rest of the questions for other DBAs.
-
Unfortunately, I can't leave the column out of the insert because I want to copy over non-default values from the database. And since I have no control over other developers or DBAs who write T-SQL, I can't force them to leave the column out either.
All I want is to keep NULLS from getting into that column if there is no other value available.
-
The only solution I can come up with is to hardcode the default value in the insert (which defeats the purpuse of having the default on the column).
You could also create a function that returns the default value from the system tables, but that's a query per column per default per insert. So that may very well affect the speeds of the inserts. That's why I don't really answer that question, I have no usefull, experienced answer on that scenario.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply