SQL更新以允许空值

I have table in MSSQL that has a column where the value can not be null. But there are update operations where i want to set that column to null when i update the other columns. Is there such a way to achieve this , or should i recreate the table , allowing the column to take null values.

我在MSSQL中有一个表，其中的值不能为null。但是有更新操作，我想在更新其他列时将该列设置为null。有没有这样的方法来实现这一点，或者我应该重新创建表，允许列采用空值。

4 个解决方案

#1

You should allow null for your column: alter table T1 alter column C1 int null

您应该为列允许null：alter table T1 alter column C1 int null

#2

You could probably do something like this:

你可能会做这样的事情：

    "ALTER TABLE table ADD COLUMN newcolumn VARCHAR(255)"
    "UPDATE TABLE table SET newcolumn = oldcolumn"
    "ALTER TABLE table DROP COLUMN oldcolumn"
    "ALTER TABLE table ADD COLUMN oldcolumn VARCHAR(255)"
    "UPDATE TABLE table SET oldcolumn = newcolumn"

#3

You will need to update the schema. There is no other way. That is the reason for constraints. You can perform an alter to avoid totally recreating the table if you have data that would not be lost, that is

您需要更新架构。没有其他办法。这就是限制的原因。如果您有不会丢失的数据，则可以执行更改以避免完全重新创建表

#4

use ALTER TABLE statement to allow null values.

使用ALTER TABLE语句允许空值。

see http://msdn.microsoft.com/en-us/library/ms190273.aspx

请参阅http://msdn.microsoft.com/en-us/library/ms190273.aspx

#1