登录
 
主页技术资源库学习下载支持社区论坛
IT 专业人士的资源 >
> 
> 
Default not working for Alter Table

已答复 Default not working for Alter Table

  • 2012年7月5日 21:06
     
      包含代码
    登录进行投票
    0

    i used the following code to add a column to a table. It was added but the value was set to NULL.  I also tried this with no quotes around the 0 so it wouldn't be treated as a string.  I don't understand why my default statement didn't work.

    ALTER TABLE MyTable ADD MyColumn int NULL DEFAULT '0'

     

全部回复

  • 2012年7月5日 21:14
     
     已答复 包含代码
    登录进行投票
    0

    If you want the default, set it to not allow nulls, otherwise it defaults to NULL:

    ALTER TABLE MyTable ADD MyColumn int NOT NULL DEFAULT 0

     
  • 2012年7月5日 21:17
     
     
    登录进行投票
    0

    Oh.  I thought it would allow nulls but still default to the value I set.  No wonder the results were not as I expected. Thanks.

    Any idea on what the logic is behind this? Especially since the default was set.  I don't understand why the compiler chooses to ignore the default if nulls are allowed. 

     
  • 2012年7月5日 21:24
     
     已答复 包含代码
    登录进行投票
    1

    You need to specify the WITH VALUES clause if you are adding a new column that allows NULLs, but you want that column to be loaded with the default value in the existing rows.

    ALTER TABLE MyTable ADD MyColumn int NULL DEFAULT 0 WITH VALUES;

    Tom


     
  • 2012年7月5日 21:29
     
     
    登录进行投票
    0
    AH.  Thanks.  I forgot about that.
     
  • 2012年7月6日 5:36
     
     
    登录进行投票
    0
    use replace function and place 0 instead of nulls