将 TIMESTAMP 列更新为可为空 [英] Update a TIMESTAMP column to be nullable

问题描述

我有一个存在于两个数据库中的表.

I have a table that exists in two databases.

在一个数据库中有一个表，其中有一列名为 ROW_VERSION 的列是 TIMESTAMP NOT NULL 类型.

In one database there is a table with a column called ROW_VERSION that is of type TIMESTAMP NOT NULL.

在第二个数据库中，同一个表具有相同的 TIMESTAMP 类型列，但它的类型是 TIMESTAMP NULL.

In the second database the same table has the same column of type TIMESTAMP but it is of type TIMESTAMP NULL.

我想将第一个数据库中的列更改为可以为空.这将使我能够更轻松地在两个数据库之间进行同步.

I would like to change the column in the first database to be nullable. This will allow me to synchronize between the two databases easier.

但是当我运行这个时:

ALTER TABLE [MyTable]
ALTER COLUMN ROW_VERSION TIMESTAMP NULL

我收到错误:

无法将列ROW_VERSION"更改为时间戳数据类型.

Cannot alter column 'ROW_VERSION' to be data type timestamp.

它已经是一个时间戳.我只需要让它可以为空.有没有办法做到这一点?

推荐答案

我不认为你可以.并且一张表只能有一个时间戳列.

I don't think you can. And a table can only have one timestamp column.

您也无法更新时间戳列，因此旧的复制/删除/更新"技巧将不起作用.

You also cannot update a timestamp column, so the old "copy/drop/update" trick won't work.

你很可能被你所拥有的束缚住了.

You are likely stuck with what you have.

create table #tmp (id int, timestamp null)
insert into #tmp (id) values (1)
select * from #tmp


create table #tmp2 (id int)
insert into #tmp2 (id) values (1)
alter table #tmp2 add timestamp null
select * from #tmp2

我正在测试 NULL TIMESTAMP 列，它实际上不会变为 NULL -- 我找不到任何说明它的文档，但我认为 NULL TIMESTAMP 是不可能的(即使声明为 null，它在数据).

I'm testing NULL TIMESTAMP columns, and it wont actually go NULL -- I can't find any documents stating it, but I don't think a NULL TIMESTAMP is possible (even if declared null, its never null in the data).

timestamp 是 rowversion 数据类型的同义词，是主题到数据类型同义词的行为.在 DDL 语句中，使用尽可能使用 rowversion 而不是时间戳.更多信息，请参阅数据类型同义词 (Transact-SQL)...

timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible. For more information, see Data Type Synonyms (Transact-SQL)...

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.110).aspx

还有...

ALTER COLUMN 指定要更改的命名列或改变了.修改后的列不能是以下任何一项:A具有时间戳数据类型的列...

ALTER COLUMN Specifies that the named column is to be changed or altered. The modified column cannot be any one of the following: A column with a timestamp data type...

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

这篇关于将 TIMESTAMP 列更新为可为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！