Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2008
    Posts
    5

    Unanswered: Accept NULL value and convert it to default value...

    Is there any way to make a column to accept NULL but stores it as default value of Column Property?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, but it is a horribly bad idea.

    -PatP

  3. #3
    Join Date
    Jan 2008
    Posts
    5

    What I want is....

    I have a column which takes bit.
    It should either 0 or 1.
    I set the default value as 0 hoping that NULL value is converted to 0, however, the actual saved data is NULL.

    So, whenever inserting a new row, if that column's value is NULL, I hope it to take it as zero.

    Is it still bad idea?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure why Pat thinks this is such a bad idea, but he'll probably jump back in and explain himself.

    If a column has a default value, that value will be used any time an insert is made that does not explicitly define a value for the column (the column is not included in the insert statement). However, if NULL is explicitly inserted into the table, the column will still accept NULL, and the value can be UPDATED to null as well since default values are not applied to UPDATE statements.

    To effect this business logic, you will need a trigger on the table that fire for both updates and inserts, that will convert NULL values to your desired default.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    quote:

    To effect this business logic, you will need a trigger on the table that fire for both updates and inserts, that will convert NULL values to your desired default

    I'm sorry to disagree but I hate the use of triggers, to me using a trigger is either bad design or lack of knowledge.

    Use COALESCE() in your insert statement like this:

    INSERT INTO TABLE (BITFIELD)
    SELECT COALESCE (BITFIELD, 0)

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by SQLSlammer
    I'm sorry to disagree but I hate the use of triggers, to me using a trigger is either bad design or lack of knowledge.
    to say this shows the same about you and triggers. the biggest problem with triggers are developers not understanding how to code them properly or when to properly use them.

    In and of themselves, they are not evil. In the right hands, a hammer is a tool and in the wrong hands it is a weapon.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Ok point taken, maybe my opinions are driven by my own experiences but...

    Personally every example of a trigger I have ever seen, can always be achieved better by someother method. If you know of a scenario where a trigger is the best method of acheiving a result then I would be happy to hear it.

  8. #8
    Join Date
    Feb 2004
    Posts
    492
    RexCho: seems to me like you only want 0 or 1. Why allow NULL?

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Kaiowas
    RexCho: seems to me like you only want 0 or 1. Why allow NULL?
    Probably because NOT NULL on the column with or without a Default value will fail.

    CREATE TABLE SHORTTEST (
    Column1 int NOT NULL,
    Column2 bit NOT NULL DEFAULT 0)

    INSERT INTO SHORTTEST
    SELECT right(rand(datepart(mm,getdate())),5),NULL

  10. #10
    Join Date
    Feb 2004
    Posts
    492
    Sure, the default only replaces a value when it's not specified. I know RexCho considers replacing one value (null) for another (0), but, IMHO, it's poor programming if you replace such a value on that level.

    I'm just saying: If you don't want it, make it so.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by SQLSlammer
    I'm sorry to disagree but I hate the use of triggers, to me using a trigger is either bad design or lack of knowledge.
    Nah. Triggers are wholly appropriate when you want to enforce some process or rule that absolutely without fail must always occur on every insert\ update\ delete and cannot be guarenteed any other way. There is then no reliance on knowledge of business rules by the developers\ admins. If someone has to apply some ad-hoc SQL via SSMS rather than the interface they won't b0rk the whole system if they omit the business rule in the SQL statement they submit.

    As it happens though, if the OP has a half decent QA environment then I would defo implement NOT NULL on the column and coalesce NULLS to 0 in all insert and update statements in this instance. I wouldn't, on balance, use a trigger here.

    EDIT - extended my "acceptable use of triggers" rule

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by SQLSlammer
    Ok point taken, maybe my opinions are driven by my own experiences but...

    Personally every example of a trigger I have ever seen, can always be achieved better by someother method. If you know of a scenario where a trigger is the best method of acheiving a result then I would be happy to hear it.
    Hmmm...I recently saw an example where a trigger was a better method of implementing a rule...where was it?....ah, yes, it was this post:
    http://www.dbforums.com/showpost.php...47&postcount=5
    That poster made the all-too-common error of separating a constraint from the table. Anybody who follows his advice will have to be sure that every developer uses COALESCE() in every single sproc, dynamic SQL statement, BCP command, or DTS package that inserts or updates into that table. Under this scenario, it is not a matter of IF the data eventually becomes corrupted, but WHEN. Had this poster encapsulated this logic into a simple trigger, he could be sure that the rule would ALWAYS be in effect, regardless of the competencies of whatever developer is writing code against the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Although blindman - the constraint would (or at least should) be the NOT NULL constraint. The coalesce is then ensuring the incoming data meets the constraint. It does not enforce the constraint itself.

    This is assuming that there is decent QA and any COALESCING errors by said developer get picked up before roll out to prod.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its a business rule tied directly to the data, and as such the rule should be stored with the data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My original objection is to the idea that a default is one thing, it provides a default value for a column when no value is specified in the corresponding SQL statement. Actively replacing one value (even NULL) that is formally provided in the SQL with a different value is a receipe for disaster. This is the kind of thing that can drive DBAs and developers to drink, thinking that the SQL Engine or at least the database has lost its mind.

    I have no problem with a default, but what you want to do just gives me the willies!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •