Altering a column: null to not null

 

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL . Aside from changing nulls to 0 , data must be preserved.

I am looking for the specific SQL syntax to alter a column (call it ColumnA ) to " not null ". Assume the data has been updated to not contain nulls.

Using SQL server 2000.

First, make all current NULL values disappear: 

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL

Then, update the table definition to disallow NULLs: 

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

我有同样的问题，但该字段默认为null，现在我想将其默认为0.这需要在mdb的解决方案之后再添加一行： 

ALTER TABLE [Table] ADD CONSTRAINT [Constraint] DEFAULT 0 FOR [Column];

You will have to do it in two steps:

  • Update the table so that there are no nulls in the column. 
    • UPDATE MyTable SET MyNullableColumn = 0
WHERE MyNullableColumn IS NULL
  • Alter the table to change the property of the column 
    • ALTER TABLE MyTable
ALTER COLUMN MyNullableColumn MyNullableColumnDatatype NOT NULL
    链接地址: http://www.djcxy.com/p/2134.html

    上一篇: 为什么null是一个对象，null和undefined有什么区别？

    下一篇: 更改列：null为非空