数据库中的复合主键 [英] Composite primary keys in databases
问题描述
我需要创建一个数据库设计，以便酒吧或事件所有者（管理员表，因为他们拥有配置文件）将警报发送给用户。
I need to create a database design where bar or event owners (admin-table because they own a profile) send alerts to users.
我想创建一个 ALERT表，但是很难决定要使用哪个主键。
我想添加一个至少包含admin_ID（PFK），user_ID（PFK）的复合键，并且我想在主键上添加日期和时间戳，以表明管理员可以发送许多警报（通知），但在某个特定时间点只有1个。
但是，从此线程发出：将时间戳记作为复合主键的一部分吗？，我了解到我不应该使用时间戳记。
顺便说一句，尚未确定我们将使用哪种DB软件。此时使用。
I want to create an "ALERT" table but have a hard time deciding what primary key to use.
I thought adding a composite key containing at least admin_ID (PFK), user_ID (PFK) and I thought to add Date and Time stamp to the primary key to indicate that many alerts (notifications) can be sent by an admin, but only 1 at a certain point in time.
However, from this thread: Timestamp as part of composite primary key?, I learned that I shouldn't use the timestamp.
By the way, it has not been decided what DB software we will use at this point.
有时我倾向于快速移至自动增量键。我从没注意到（在Access中）的问题，但是从我的阅读中来看，这可能并不总是最有意义的事情，因此我向专业人员提出这个问题。
我只有一次机会用正确的方法从根本上使后端正确。
您对此有何看法？
I sometimes have the tendency to quickly move to an autoincrement key. I have never noticed problems with that (in Access), however from what I read, this may not always be the most meaningful thing to do, therefore I ask the question to professionals.
I only have one chance to do this the right way to make the back end fundamentally right.
What are your thoughts on this?
推荐答案
您将对使用PK的问题引起很多激烈的争论。纯粹主义者会告诉您，永远不要使用自动增量键（假设使用SQL SERVER）。曾经去过真正战es的人会告诉你，他们在PK（大多数情况下）方面表现出色，并且拥有一些真正的优势。
Your going to get a lot of heated debate over the issue of what to use for a PK. A purist will tell you that you should never use an auto-increment key (assuming SQL SERVER). Someone who's been in the real trenches will tell you they are perfectly fine as a PK (in most cases) and have some real advantages.
我不会说服您的一种方式。但是我会告诉你我的经验。我从事软件开发已有25年了，并且在大部分时间里一直在使用RDBMS（主要是SQL Server）。就个人而言，我发现自动递增的PK非常有用，并且99.99％的时间永远不会考虑使用其他任何方法。为什么？
I won't try to persuade you one way or the other. But I will tell you my experience. I have been developing software for 25 years and have been using an RDBMS for much of that time (mostly SQL Server). Personally, I find auto-increment PKs invaluable and 99.99% of the time would never consider using anything else. Why?
- SQL Server生成它们，因此可以处理所有并发问题。
- 它们是尺寸很小，因此在这些键上的查找非常快。
- 您可以通过其ID值轻松引用表中的特定行。这听起来似乎没什么大不了的，但是肯定可以派上用场。例如，我无法告诉您我曾让一位联合开发者看过表中键值为12345的行的次数。这很简单，但非常有用。
- 在表中引用PK的FK的类型相同，因此又小又快，并且易于使用。
- 索引的碎片很少或没有碎片，
- SQL Server generates them and, therefore, handles all concurrency issues.
- They are small in size and, therefore, lookups on these keys are very fast.
- You can easily refer to a particular row in a table by its Id value. This may not sound like a big deal, but it can sure come in handy. For example, I cannot tell you how many times I've asked a co-developer to take a look at a row in a table with key value 12345. A simple thing, but very useful.
- FKs that reference a PK in a table will be the same type, and, therefore, also small and fast, and easy to work with.
- Little or no fragmentation of the index, especially if the PK is a clustered index (in SQL Server).
这种PK可能有一个很大的不利之处。尤其是如果PK是聚集索引（在SQL Server中）。如果必须将行从一个数据库合并到另一个数据库，则可能会遇到PK值冲突。但是，也有解决此问题的方法。而且，当然，自动增量值将没有真正的意义。但是没关系。它的目的是提供唯一性。
There can be one big disadvantage with this sort of PK. If you ever have to merge rows from one database to another, you have the potential of running into PK value collisions. However, there are ways to work around this as well. Also, of course, an auto-increment value will have no true meaning. But that's ok. Its purpose is to provide uniqueness.
这是一个完美的解决方案。不。其他人则不同意使用它们。但是，对于大多数高端且对业务至关重要的项目，它们对我来说都很有效。
Is this a perfect solution. Nope. And others will disagree with the use of them. However, they have worked very well for me, for most high-end, and business critical, projects.
这篇关于数据库中的复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
