none
Default not working for Alter Table

    问题

  • 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:06

答案

  • 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:24
  • 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:14

全部回复

  • 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:14
  • 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:17
  • 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:24
  • AH.  Thanks.  I forgot about that.
    2012年7月5日 21:29
  • use replace function and place 0 instead of nulls 
    2012年7月6日 5:36