我需要更改表的唯一 ID 列
不确定执行此操作的最佳方法，或者是否有一个最佳答案，但问题是：
我有一个包含至少 13 个表的 SQL Server 2000 数据库。某些表具有唯一的 ID (SysName)，因此只能有一个唯一的 SysName 值（alpha001、alpha002、alpha003 等） ）。
到目前为止和我在一起吗？
现在，客户端希望能够在数据库中插入多个 sysnames 值（alpha001、alpha001、alpha001、alpha002、alpha002、alpha003、alpha003、alpha003、alpha003、alpha003 等）。最重要的是，如果您更改一个表中的系统名值（alpha001 到 beta001），其他一些表中的值也会更新（我现在对这叫什么感到很头疼） 。
我还想添加一个新列 (SysNameID) 并将其设置为自动增量。执行此操作的最佳方法是什么，以便它将遍历数据库并仅添加 SysNameID 值（1,2,3,4,5,6...），这样我就不会必须重建表吗？
我想我需要向所有受影响的表添加一个 SysNameID 列，并在表之间设置（脑放屁术语）以保留相关性（出于数据完整性目的）。
如果此请求不清楚，请发表评论，我会尽力回答。你们中的一些人非常聪明，所以你们可能需要为我简化一下。 :)
Not sure the best way to do this or if there is even a single best answer but here is the problem:
I have a SQL Server 2000 database that consists of at least 13 tables. Some of the tables have a unique id (SysName) so that there can only be one unique SysName value (alpha001, alpha002, alpha003, etc).
With me so far?
Now, the client is wanting to be able to insert multiple sysnames values in the database (alpha001, alpha001, alpha001, alpha002, alpha002, alpha003, alpha003, alpha003, alpha003, alpha003, etc). On top of that, if you change the value for a sysname (alpha001 to beta001) in one table, the value in some of the other tables are also updated (I'm having a brain fart about what this is called at the moment).
I'd also like to add a new column (SysNameID) and set it to autoincrement. What is the best way to do this so that it will go through the database and just add the SysNameID values (1,2,3,4,5,6...) so that I do not have to rebuild the table?
I'm thinking I need to add a SysNameID column to all the affected tables and set the (brain fart term) between the tables to retain the correlation (for data integrity purposes).
If this is request is unclear, please leave a comment and I'll do the best I can to answer it. Some of you guys are very smart so you may have to dumb it down for me. :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
基本过程：
确保您有当前的备份，并且在没有首先对开发进行全面测试的情况下，不要尝试在产品上执行此操作。这是一个如此广泛的更改，您可能希望将产品恢复到新的开发实例，因为这将非常耗时且棘手，并且在执行此操作时其他开发将受到干扰。
您将标识列添加到名为 SysNameID 的父表（有关详细信息，请参阅 @marc_s' 答案）
您将 int 列添加到每个也称为 SysNameId 的子表。它不是自动增量列，必须允许空值。
您可以通过使用当前的 sysname 列来查找与该 sysname 关联的 id 来更新此列。
填充所有列后，您将列设置为不允许空值并创建父表的外键。代理键不应更改，因此您实际上不需要级联更新。
最后，从子表中删除 sysname 列，并调整使用它连接到父表并查找它的所有代码。或者，您可以重命名每个子表并创建一个视图，将子表连接到父表并从那里获取 sysname 列。这应该确保现有代码不会被破坏。
没有简单的方法可以满足您的要求。您正在改变数据库工作方式的根本原理。它有可能影响几乎针对子表的每个查询。它可能会影响报告（可能按系统名排序，现在不再唯一）。这是一个重大的改变，要做好它可能需要几个月的时间。
建议阅读：
http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr_1_1?ie=UTF8&s=books&qid=1268158669&sr=8-1
Basic process:
Make sure you have a current backup and do not attempt to do this on prod without a through test on develpment first. This is so extensive a change, you might want to restore prod to a new dev instance because it will be time-consuming and tricky and other development will be interfered with while you do this.
You add the identity column to the parent table called SysNameID (see @marc_s' answer for details)
You add an int column to each child table also called SysNameId. It is not an autoincrementing column and must allow nulls.
You Update this column by using the current sysname columns to find the id assciated with that sysname.
Once all the columns are populated you set the column to not allow nulls and create the foreign key to the parent table. The surrogate keys should not change, so you don;t really need to cascade update.
Finally you drop the sysname column from the child tables and adjust all the code that uses it to join to the parent table and look it up. Alternatively, you rename each child table and create a view that joins the child table to the parent and gets the sysname column from there. That should ensure existing code doesn't break.
There is no simple way to meet your requirement. You are changing the very fundamentals of how your database works. It has the potential to affect virtually every query against the child tables. It may affect reports (which are likely sorted by sysname which is now no longer unique). This is a major change and to do it properly could take months.
Suggested reading:
http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr_1_1?ie=UTF8&s=books&qid=1268158669&sr=8-1
我不完全理解当前
sysname列的问题/挑战是什么......但对于第二部分：是的，您可以轻松地将自动增量列添加到表中，不，你不需要做任何事情（比如“重建表”——无论它应该是什么......） - SQL Server将为你填充现有的行自动增量值。$
我通常建议该
IDENTITY列要么是您的表的主（和聚集）键，或者如果不可能，至少在列上放置一个唯一约束以防止插入重复值：I don't totally understand what your question/challenge is with the current
sysnamecolumn ....but for the second part: yes, you can easily add an autoincrement column to your table(s), and no, you won't have to do anything (like "rebuild the table" - whatever that was supposed to be.....) - SQL Server will fill the existing rows with autoincrement values for you.$
I usually recommend making that
IDENTITYcolumn either your primary (and clustered) key for the table, or if that's not possible, at least put a UNIQUE CONSTRAINT on the column to prevent duplicate values from being inserted: