表的 ALTER TABLE ALTER COLUMN 失败，因为它是分类帐表，并且该操作需要修改不可变的现有数据
我想将列从日期更新为日期时间。
当我运行查询时，我收到以下消息：
ALTER TABLE ALTER COLUMN 对于表“Transaction”失败，因为它是一个分类帐表，并且该操作需要修改不可变的现有数据。
执行 DbCommand 失败（40 毫秒）[参数=[]，CommandType='Text'，CommandTimeout='30']
声明 @var0 系统名；
SELECT @var0 = [d].[名称]
FROM [sys].[default_constraints] [d]
内连接 [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[交易]') AND [c].[name] = N'日期');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [事务] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [事务] ALTER COLUMN [日期] 日期时间 NOT NULL;
我明白问题所在，但我没有解决方案。
我是否必须删除数据库并从头开始？我需要使用相同的列名，因此创建另一列然后将数据复制到新列不是一个选项。
我似乎无法删除或更新相应的分类帐表及其记录。
推荐的方法是什么？使用分类帐表是否意味着我永远无法删除或更改任何列？
任何意见或答案将不胜感激！
I wanted to update a column from date to datetime.
When I run the query I get the following messages:
ALTER TABLE ALTER COLUMN failed for table 'Transaction' because it is a ledger table and the operation would need to modify existing data that is immutable.
Failed executing DbCommand (40ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @var0 sysname;
SELECT @var0 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Transaction]') AND [c].[name] = N'Date');
IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [Transaction] DROP CONSTRAINT [' + @var0 + '];');
ALTER TABLE [Transaction] ALTER COLUMN [Date] datetime NOT NULL;
I understand what the problem is, but I do not have a solution.
Do I have to delete the database and restart from scratch? I need to use the same column name so creating another column and then copy the data to the new column is not an option.
I cannot seem to delete or update the corresponding ledger tables and their records.
What would be the recommended approach? And does using ledger tables means that I will never be able to drop or alter any columns?
Any comments or answers would be much appreciated!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您创建了仅追加分类账表，您将无法更新或删除记录。只允许插入。您仍然可以删除或更改表中的列，但需要考虑一些注意事项。
请参阅 分类帐注意事项和限制
If you have created an append-only ledger table, you won't be able to update or delete records. Only insert is allowed. You can still drop or alter columns in a table but there are some considerations that you will need to take into account.
See Ledger considerations and limitations