将主键添加到具有现有聚集索引的表
我必须使用数据库来进行报告 DB 相当大： 416 055 104 行 不过，每一行都很轻，只有布尔值和 int id。
每行由 3 列标识，但令我惊讶的是，它上面没有主键。 只有具有唯一约束的聚集索引。
所以知道了这一点，我有两个问题。
- 这有什么充分的理由吗？
- 有什么办法可以把它变成主键吗？
关于问题 2
创建新主键还会创建一个与之关联的非聚集索引（已经存在一个聚集索引）。
这不是我正在寻找的东西。我想保留相同的索引，但也将其设为主键。
- 是否可以？
- 这会比再次创建整个索引更快吗？ （我希望如此）
- 会有什么后果？ （锁？崩溃？数据损坏？）
I have to work with a database to do reporting
The DB is quite big : 416 055 104 rows
Each row is very light though, just booleans and int ids.
Each row is identify by 3 columns, but at my surprise, there is no Primary Key on it.
Only a Clustered Index with a unique constraint.
So Knowing that, I have 2 question.
- Could there be ANY good reason for that?
- Is there any way I can turn this into a primary key.
Regarding question 2
Creating a new primary key also creates a non-clustered index to associate with (there is already an existing clustered one).
This is not what I am looking for. I want to keep that same index, but also make it a primary key.
- Is it possible?
- Would that be faster that creating the whole index again? (I hope so)
- What could be the consequences? (locks? crash? corrupted data?)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不可空列上的 PRIMARY KEY 和 UNIQUE 约束之间几乎没有区别。因此，如果相关列不可为空，那么我建议您什么也不做。将候选键设为主键的主要原因是，如果您有某些软件（例如数据建模工具或其他开发工具）期望使用 PRIMARY KEY 约束来标识该键。
There is little or no difference between a PRIMARY KEY and a UNIQUE constraint on non-nullable columns. So if the columns in question are non-nullable then I suggest you do nothing. The main reason to make a candidate key into a primary key is if you have some software (such as a data modelling tool or other development tool) that expects the key to be identified with a PRIMARY KEY constraint.
好问题。
如果您在不可为空的列上已经有唯一索引，那么您就有了一个候选键。我不知道将其作为“官方”主键有什么特别的好处。事实上我有一种感觉，不把它做成PK会带来更大的灵活性。
Good question.
If you already have a unique index on non nullable columns then you have a candidate key. I'm not aware of any particular benefit of making this an "official" primary key. In fact I have a feeling that not making it a PK will give greater flexibility.
唯一索引可以允许为空
价值观。主键不能。
我相信您不能将现有索引“标记”为主键。您必须删除它并重新创建。为了避免出现问题，我建议在执行此操作之前在表上放置一个
TABLOCKX, HOLDLOCK。A unique index can allow null
values. A primary key can't.
I believe you can't "mark" an existing index as the primary key. You'd have to drop it and recreate. To avoid stuff, I'd say it'd be good to place a
TABLOCKX, HOLDLOCKon the table before doing that.