不允许为空且在现有表中不指定DEFAULT定义 [英] Does not allow nulls and does not specify a DEFAULT definition in existing table

问题描述

我的表包含一些行.
我想添加一个不允许为null的bigint数据类型列，并分配默认值"0"
但出现错误消息.
错误消息是" ODBC错误:[Microsoft] [ODBC SQL Server驱动程序] [SQL Server] ALTER TABLE仅允许添加可以包含空值或具有DEFAULT定义的列.无法将列``Column_name''添加到表``Table_Name''中，因为它不允许为空且未指定DEFAULT定义.''

如何在现有表中分配不允许的空值并且不指定DEFAULT定义?
请帮助我解决问题的任何指示...

I have table that contain some rows.
I want to add a bigint data type column with not allow null & assign a default value ''0''
But arise a error msg.
The error msg is ''ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column ''Column_name'' cannot be added to table ''Table_Name'' because it does not allow nulls and does not specify a DEFAULT definition.''

How will i do to assign a not allow nulls and does not specify a DEFAULT definition in existing table??
Please help me any instruction for solving......

推荐答案

您不能.

由于您已有数据，因此在添加列时，必须扩展现有数据以包括新信息.如果不能为null，则新值必须来自某个地方，而该地方必须是默认值.

您唯一的另一种选择是删除该表，然后在使用当前信息重新填充该表之前重新创建该表-但随后您将不得不计算出每行新值应该是什么. IE.您必须自己计算默认值".

You can''t.

Since you have existing data, when you add a column the existing data has to be extended to include the new info. If it can''t be null, then the new values have to come from somewhere, and that place has to be the default value.

Your only other alternative is to delete the table, and recreate it before re-populating it with the current info - but then you will have to work out what each row new value should be. I.e. you will have to work out the "default value" yourself.

将NOT NULL列作为在线添加操作

在SQL Server 2012 Enterprise Edition中，当默认值为运行时常量时，添加具有默认值的NOT NULL列是一项联机操作.这意味着无论表中的行数是多少，该操作几乎都是立即完成的.这是因为表中的现有行在操作期间不会更新；相反，默认值仅存储在表的元数据中，并且在访问这些行的查询中根据需要查找该值.此行为是自动的；除了ADD COLUMN语法外，无需其他语法即可实现在线操作.运行时常量是一个在运行时为表中的每一行产生相同值的表达式，无论其确定性如何.例如，常量表达式我的临时数据"或系统函数GETUTCDATETIME()是运行时常量.相反，函数NEWID()或NEWSEQUENTIALID()不是运行时常量，因为为表中的每一行生成了唯一值.始终离线执行添加具有非运行时常量默认值的NOT NULL列的操作，并且在操作期间将获得排他(SCH-M)锁.

现有行引用存储在元数据中的值时，对于插入的任何新行，默认值存储在该行中，并且不为该列指定其他值.更新行时(即使未在UPDATE语句中指定实际的列)，或者重建表或聚集索引时，存储在元数据中的默认值也会移动到现有行.
不能在联机操作中添加类型为varchar(max)，nvarchar(max)，varbinary(max)，xml，text，ntext，image，hierarchyid，geometry，geography或CLR UDTS的列.如果这样做会导致最大可能的行大小超过8060字节限制，则无法在线添加列.在这种情况下，该列将作为脱机操作添加.

In SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

While the existing rows reference the value stored in metadata, the default value is stored on the row for any new rows that are inserted and do not specify another value for the column. The default value stored in metadata is moved to an existing row when the row is updated (even if the actual column is not specified in the UPDATE statement), or if the table or clustered index is rebuilt.

Columns of type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography, or CLR UDTS, cannot be added in an online operation. A column cannot be added online if doing so causes the maximum possible row size to exceed the 8,060 byte limit. The column is added as an offline operation in this case.

来源: ALTER TABLE(Transact-SQL) [ ^ ]

Source: ALTER TABLE (Transact-SQL)[^]

这篇关于不允许为空且在现有表中不指定DEFAULT定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！