用PK插入到SQL Server表中特定位置的行中 [英] Insert into a row at specific position into SQL server table with PK

问题描述

我想在SQL Server表的特定位置插入一行.例如，我的表有100行，我想在位置9插入新行.但是，表的PK的ID列已经有ID为9的行.如何在此位置插入行，以便所有移到下一个位置后的行?

I want to insert a row into a SQL server table at a specific position. For example my table has 100 rows and I want to insert a new row at position 9. But the ID column which is PK for the table already has a row with ID 9. How can I insert a row at this position so that all the rows after it shift to next position?

推荐答案

关系表没有位置".作为优化，索引将按指定的键对行进行排序，如果您希望按键顺序在特定等级插入行，请使用在该等级位置排序的键插入行.对于您的情况，您必须使用ID大于8的值更新所有行，以将ID递增1，然后插入值为9的ID:

Relational tables have no 'position'. As an optimization, an index will sort rows by the specified key, if you wish to insert a row at a specific rank in the key order, insert it with a key that sorts in that rank position. In your case you'll have to update all rows with a value if ID greater than 8 to increment ID with 1, then insert the ID with value 9:

UPDATE TABLE table SET ID += 1 WHERE ID >= 9;
INSERT INTO TABLE (ID, ...) VALUES (9, ...);

不用说，做这样的事情不可能有任何理智的理由.如果您确实有这样的要求，则可以使用包含两个(或更多)部分的复合键.这样的键将允许您插入子键，以便按所需顺序排序.但是更有可能完全通过指定正确的ORDER BY来解决您的问题，而不必弄乱行的物理顺序.

Needless to say, there cannot possibly be any sane reason for doing something like that. If you would truly have such a requirement, then you would use a composite key with two (or more) parts. Such a key would allow you to insert subkeys so that it sorts in the desired order. But much more likely your problem can be solved exclusively by specifying a correct ORDER BY, w/o messing with the physical order of the rows.

另一种看待它的方法是重新考虑主键的含义:实体的标识符，在该实体的生存期内不会更改.然后，您的问题可以用某种方式改写，使您的问题的谬误更加明显:

Another way to look at it is to reconsider what primary key means: the identifier of an entity, which does not change during that entity lifetime. Then your question can be rephrased in a way that makes the fallacy in your question more obvious:

我想将ID 9的实体的内容更改为新的 价值.实体9的旧值应移至内容 ID为10的实体的旧内容.ID为10的实体的旧内容应为 移到ID为11的实体...等等.老人 ID最高的实体的内容应作为新的插入 实体.

I want to change the content of the entity with ID 9 to some new value. The old values of the entity 9 should be moved to the content of entity with ID 10. The old content of entity with ID 10 should be moved to the entity with ID 11... and so on and so forth. The old content of the entity with the highest ID should be inserted as a new entity.

这篇关于用PK插入到SQL Server表中特定位置的行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！