sql中每条记录有多个外键？
我正在创建一个应用程序（使用 PHP / Codeigniter / MYSQL）来跟踪活动中的志愿者。我希望有多名志愿者能够报名参加每个活动。我计划使用名为 signup 的表来执行此操作，该表如下所示：
TABLE SIGNUP
============
VolunteerId EventId
----------- -------
12 223
13 223
15 223
12 235
13 235
19 235
两列都是外键（对于 volunteer 表和 event< 的主键/code> 表分别）。
有更好的方法吗？ 我应该使用复合键作为主键吗？
I'm creating an application (using PHP / Codeigniter / MYSQL) for tracking volunteers at events. I'd like multiple volunteers to be able to sign on to each event. I plan on doing this using a table called signup which looks something like this:
TABLE SIGNUP
============
VolunteerId EventId
----------- -------
12 223
13 223
15 223
12 235
13 235
19 235
Both columns are foreign keys (to the primary keys of the volunteer table and event table respectively).
Is there a better way to do this?
Should I use a compound-key as the primary key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
老实说，我不认为你的设置方式有问题。像这样的表通常用于在不同对象之间建立一对多关系。我正在一个引用给定州的县和城市的表中做类似的事情。 （有些城市跨越多个县。）
数据库设计最佳实践指出，您应该为表声明主键。你不必这样做；从技术上讲，您可以声明一个没有主键的表。但是，请注意，如果您没有专门声明一个键，许多数据库引擎只会在幕后为您创建一个主键；然而，这可能并不适合所有情况（通常也不是）。指定您选择的主键有利于数据库优化和组织。
因此，我想说您最好使用复合键作为多对多表的主键，而不是创建单独的索引列。在这种情况下，这将满足表要求（因为数据库引擎无论如何都会为您创建主键），并且它将防止同一对多次出现，这在多对多中不会给您带来任何好处参考表。
简短回答：使用复合主键 -
primary key(VolunteerID, EventID)。你不应该出错。Honestly, I don't see a problem with the way you've set it up. Tables like this are commonly used to establish one-to-many relationships between different objects. I'm doing something similar in a table that references counties and cities in a given state. (Some cities span multiple counties.)
Database design best practices state that you should declare a primary key for a table. You don't have to do this; you can technically declare a table without a primary key. However, note that many DB engines will simply create a primary key for you behind the scenes if you don't specifically declare a key; this, however, may not be ideal for every situation (and generally isn't). Specifying a primary key of your choice is good for database optimization and organization.
Due to this, I'd say that you might as well use a compound key as your primary key for your many-to-many table instead of creating a separate index column. In this situation, this will satisfy the table requirements (as a db engine will make a primary key for you regardless) and it will prevent multiple occurences of the same pair, which won't do you any good in a many-to-many reference table.
Short answer: Go with the compound primary key -
primary key(VolunteerID, EventID). You shouldn't go wrong.复合唯一键的一种用途是防止同一志愿者/事件对在表中出现两次。为此不需要主键。
One use for a compound UNIQUE key would be to prevent the same volunteer/event pair from appearing twice in the table. There's no need for a primary key for this.
关于为什么应避免复合主键的一个很好的讨论：使用复合/复合主键有哪些缺点？
A good discussion on why compound primary keys should be avoided: What are the down sides of using a composite/compound primary key?
鉴于您所描述的表格，您有三个选择
1 - Lunchmeat317
2 - Ted Hopp
3 - ic3b3rg
正如托马斯指出的那样，1 和 2 之间的主要区别在于 Unique 不会停止以下操作。
但是，如果这些字段不允许以空值开头（也不应该），那么它们是完全相同的。
您还可以添加代理键 (SignUpID)，如 ic3b3rg 建议的那样。但正如 CJ Date 注释（我正在解释）引入了一种人工的替代品，非易失性密钥通常是一个好主意，但由于通常很难确定波动性，因此没有正式的方法可以知道您何时真正需要它。
也就是说，只要这个表是......
......然后用不朽的话来说Yogi Berra 的“当你来到岔路口时，走它”这意味着所有三个选择都是有效的，并且该选择可能不会以某种方式影响您的系统。
就我个人而言，这就是我通常的做法。
Given the table you've described you have three choices
1 - lunchmeat317
2 - Ted Hopp
3 - ic3b3rg
As Thomas pointed out the main difference between 1 and 2 is that Unique doesn't stop the following.
However if these fields don't allow nulls to begin with (and the shouldn't) then they're exactly the same.
You could also add, as ic3b3rg suggests a Surrogate key (SignUpID). But as CJ Date notes (and I'm paraphrasing) introducing an artificial, surrogate, nonvolatile key will often be a good idea, but since its often difficult to determine volatility there's no formal way to know when you really need it.
That said as long as this table is is ...
... then in the immortal words of Yogi Berra "When you come to a fork in the road, take it" Meaning all three choices are valid and the choice probably won't impact your system one way or another.
Personally this is how I typically do it.