使用 PK 将特定位置的行插入到 SQL Server 表中
我想在 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.
相关文章