具有自动标识列的分区表 [英] Partition table with a auto identity column

问题描述

嗨大家好，



我有一个包含一些列的表和一个主键列作为整数和自动标识（自动增加1）。

我试图通过将旧数据移动到另一个表来对此表进行分区。因此我创建了一个这样的视图：

Hi Guys,

I have a table with some columns and an primarykey column as integer and auto identity (auto increament by 1).
I am trying to partition this table by moving the old data to another table.Thus I have created a view like this:

CREATE VIEW [dbo].[JoinTraps]
AS
SELECT     *
From DB2.dbo.OldTable
UNION ALL
SELECT     *
FROM    DB1.dbo.NewTable

（我还在我的自动标识栏上定义了我的支票CONSTRAINT。）



如果我没有自动识别功能，那么每件事情都可以。但当我有它插入和更新失败!!

有没有办法分区一个自动标识列作为主键的表？！



（我已经测试了不同列上的不同检查约束但没有成功。）



谢谢。

(I have also define my check CONSTRAINT on my auto identity column.)

If I don't have Auto Identity every things work fine. but when I have it Insert and update fails!!
Is there any way to partition a table which has auto an identity column as primary key?!

(I have tested different check constraint on different columns but no success.)

Thanks.

推荐答案

归档表上的标识列没有逻辑意义。密钥应该继续是orignal表中的密钥，否则与其他表等的关系将会失控，审计信息可能会丢失。

另一件事就是将这样的表拆分应该不需要。如果遇到性能问题，则应该查看数据库规范化和索引优化。通过将此表拆分为两个，您将导致规范化问题：外键约束将被影响，连接条件变得混乱，并且您将需要在这些表上具有三个类别的SQL（一个用于新表，一个用于旧表，一个用于旧表）对于所有人来说。

如果你必须按照你的意思行事（例如有人坚持这样做）并且你对数据库设计有一定的控制权，可以考虑将int主键交换到GUID中的每一个是全局唯一的。

The Identity column on the archive table doesn't make logical sense. The key should continue to be the one from the orignal table, otherwise relationships to other tables etc will go out of whack and audit information might be lost.
The other thing is that splitting the table like this should not be needed. If you are having performance problems, you should look at your database normalisation and index optimisation. By splitting this table into two you will cause normalisation problems: foreign key constraints will be effected, join conditions mucked up, and you will need have three "categories" of SQL over these tables( one for the new, one for the old and one for all).
If you must do what you are saying (e.g. someone is insisting on it) and you have some control over the DB design consider swapping the int primary key over to a GUID each of these is globally unique.

我认为你必须将自动同一性计数器设置为一个比旧表中最大值大的起始值。如果您在视图中插入，那么主键中已经存在具有相同值的列，而这是不可能的。如果在主键列中有两个具有相同值的列，那么更新它将是相同的。



如果设置自动身份的起始值反对1大于oldtable中的最大值你应该没问题。



希望有所帮助！



最好的问候，

Manfred

I reckon that you'd have to set the autoidentity counter to a start value that is one greater than the largest value in the old table. If your insert into the view there would already be a column with that same value in the primary key and that just can't be. When updating it would be the same thing if there were to be two columns with the same value in the primary key column.

If you set the start value for the autoidentity counter to 1 greater than the largest value in oldtable you should be fine.

Hope that helps!

Best Regards,
Manfred

尝试在视图上使用INSTEAD OF INSERT触发器，然后在其中写入：INSERT INTO（此处有效表名）SELECT （列）FROM INSERTED

Try using INSTEAD OF INSERT trigger on the view, and then in it, write: INSERT INTO (active table name here) SELECT (columns) FROM INSERTED

这篇关于具有自动标识列的分区表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！