数据库;一两个独特的领域？
我将使用一个名为 ID 的字段；整数主键。还指定第二个唯一字段是否有意义，以便在某些 ID 字段/关系混乱时能够重建表，或者这不值得担心吗？
我正在使用自动增量整数和 Sql-CE 数据库。单用户。
I will use a field named ID; an integer primary key. Is it any point to also specify a second unique field, to be able to reconstruct the tables if some ID fields/relationships are messed up, or is that not a worry?
I'm using auto-increment integer, with Sql-CE database. Single user.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果表的语义需要它，则可以根据需要添加任意数量的唯一键。例如，如果在您的问题域中，所有“员工”实体均由名称唯一标识，则名称应该具有唯一索引或约束。如果可以有两个同名的员工，那么该列当然不应该有唯一性约束。
我现在有一个客户需要保存每个学校、每个财政年度的信息。他们需要对学校和年份列进行组合的唯一约束（或索引）。有时这两个也是主键，但即使它们不是主键，它们也是唯一的。
If the semantics of your table require it, then add however many unique keys as you may need. For instance, if in your problem domain all "Employee" entities are uniquely identified by Name, then Name should have a unique index or constraint on it. If there can be two employees with the same name, then, of course, there should be no uniqueness constraint on that column.
I have a client now who needs to keep information per-school, per-fiscal year. They need a unique constraint (or index) on the school and year columns, combined. Sometimes these two are also the primary key, but even when they're not primary, they are unique.
我不太明白您添加第二个唯一 ID 字段的动机 - 有何用意？您从中看到什么好处？
您需要的是一个唯一、稳定且能够清楚识别每一行并可用作您的主键的
ID- 一个INT IDENTITY 做得很好。我没有看到向每一行添加第二个唯一ID有任何意义或好处......
I don't really understand your motivation to add a second unique ID field - what for?? What's the benefit you see from that??
What you need is an
IDthat is unique, stable, and able to clearly identify each individual row and can serve as your primary key - anINT IDENTITYin SQL Server does that perfectly well.I don't see any point or gain in adding a second unique ID to each row...
每个表都有一个或多个候选键。选择一个作为主键；对其他约束添加 UNIQUE 约束以强制语义正确性。如果某些东西“搞砸了”，它与能够重建表格无关。
您还应该在 WHERE 子句中出现的列上建立索引，以便尽可能快地进行访问。这些是特定于应用程序/用例的。
我的意思的一个例子是地址表。您可能有一个代理主键。您还可以对邮政编码、城市、省份、县等的不同组合建立索引，以提高 SELECT 的效率。您可能还希望 street1/stree2/city/province/zip 组合是唯一的。取决于您的业务问题。
Every table has one or more candidate keys. Pick one to be the primary key; add UNIQUE constraints on the others to enforce semantic correctness. It has nothing to do with being able to reconstruct the table if something is "messed up".
You should also have indexes on columns that appear in WHERE clauses to make access as fast as possible. Those are application/use case specific.
An example of what I mean would be an address table. You might have a surrogate primary key. You'd also have indexes on different combinations of zip, city, province, county, etc. to make SELECTs efficient. You might also want to have the street1/stree2/city/province/zip combination to be UNIQUE. Depends on your business problem.
表应该具有尽可能多的键以保证数据完整性。如果在这种情况下“ID”意味着代理键，那么答案是肯定的，您通常应该有一个自然键来确保正确规范化的数据库中的唯一性 - 否则您可能会复制有意义的业务数据。如果您在自然键之前识别代理键，那么我认为您采用了有缺陷的设计方法。
选择密钥的良好标准是：熟悉、简单和稳定。仅当您有充分理由时才将代理键添加到您的模型中，并考虑到可能的缺点和额外的复杂性。
A table should have as many keys as it needs to endure data integrity. If "ID" in this instance means a surrogate key then the answer is yes, you generally ought to have a natural key to ensure uniqueness in a properly normalized database - otherwise you could be duplicating meaningful business data. If you are identifying surrogate keys before natural keys then I think you are taking a flawed approach to design.
Good criteria for choosing keys are: familiarity, simplicity and stability. Add surrogate keys to your model only when and where you have good reason to, having regard the possible disadvantages and additional complexity.
为了备份而进行冗余可能是一个值得的设计。
但是，正如您的问题所暗示的那样，大多数人不会通过将冗余列加载到架构表中来实现它。这样做会产生大量开销，并且存在许多故障模式，其中重复 ID 在主 ID 被搞砸的同时也被搞砸了。
您最好设计历史表来跟踪某些有限时间跨度内的 ID 历史记录。很多人将历史表设计为离线存储的暂存区，数据库中只保留最近的历史记录。除了您感兴趣的问题之外，这种历史还可以解决其他问题。
Redundancy for the sake of backup can be a worthwhile design.
However, most people do not implement it by loading redundant columns into the schema tables, as your question suggests. There is a lot of overhead in doing it that way, and there are a lot of failure modes where the duplicate ID gets screwed up at the same time that the main ID gets screwed up.
You might be better off designing history tables that keep track of the history of IDs over some limited timespan. A lot of people design history tables as a staging area for off line storage, with only recent history retained in the database. This kind of history can address other problems in addition to the one you are interested in.