关于复合主键的问题
示例表：
Ticket
- id
- tenant_id
- foo
TicketItem
- id
- tenant_id
- ticket_id
- bar
假设每个表上的 id 和 tenant_id 组成复合主键，并且 ticket_id 是 Ticket 的外键 此设置是否可以保护我免受 TicketItem 具有 tenant_id=1 和 ticket_id=5 的情况的影响，其中 Ticket 与 id=5 具有 tenant_id=2？简而言之，数据库是否允许我将 2 个表中的行（每个表都有不同的 tenant_id）链接在一起，从而破坏我的数据，还是可以保护我免受这种情况的影响？
另外，上面的示例看起来是复合主键的“良好”使用吗？
Example table:
Ticket
- id
- tenant_id
- foo
TicketItem
- id
- tenant_id
- ticket_id
- bar
Assuming that id and tenant_id on each table make up composite primary keys, and that ticket_id is a foreign key to Ticket will this setup protect me from a circumstance where a TicketItem has tenant_id=1 and ticket_id=5 where the Ticket with id=5 has tenant_id=2? In simpler words, would the database allow me to link rows from 2 tables - each with different tenant_id - together, ruining my data, or does it protect me from this?
Also, does the above example seem like a "good" use of a composite primary key?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您的
Ticket表的主键为(TicketID, TenantID)，则任何引用Ticket表的表也必须引用 两列，例如，您不能仅引用（复合）主键的一部分，例如，您不能让
TicketItem中的TicketID引用Ticket表 - 在引用它的每个外键中，您都需要复合主键的两个部分（在我看来，复合索引的主要缺点之一 - 它使连接变得麻烦）IF your
Tickettable has a primary key on(TicketID, TenantID), then any table referencing theTickettable would also have to reference both columns, e.g.You cannot have a reference to just parts of a (compound) primary key, e.g. you cannot have
TicketIDinTicketItemreference theTickettable - you need both parts of a compound primary key in every single foreign key referencing it (one of the major drawbacks of compound indices, in my opinion - it makes joins cumbersome)如果我理解正确的话 - TicketItem 中的外键应该引用 Ticket 表中的 id 和tenant_id 字段。外键应该引用主键 - 如果您只引用 id，则不会引用票证表的主键，因为票证表包含一个包含 id 和tenant_id 字段的复合键。
如果 TicketItem 中的外键引用了 Ticket 表的主键（id 和tenant_id），那么您将无法在 TicketItem 表中插入/更新记录，而该记录在 TicketItem 表中没有对应的 id +tenant_id 记录。票务表（这就是你想要的）。
门票项目：
外键应引用ticket_id -> Ticket.id 和租户_id -> Ticket.tenant_id
至于复合密钥的“良好”使用 - 这取决于您的设计/要求，但它没有任何“坏”之处。
If I understand you correctly - the foreign key in TicketItem should reference both the id and tenant_id fields in the Ticket table. A foreign key should reference a primary key - if you were to only reference the id, you would not be referencing the primary key of the ticket table, as the Ticket table contains a composite key that includes both the id and the tenant_id fields.
If you have a foreign key in TicketItem that references the Ticket table's primary key (both id and tenant_id), then you will not be able to insert/update a record in the TicketItem table that does not have a corresponding id + tenant_id record in the Ticket table (this is what you desire).
TicketItem:
Foreign Key should reference ticket_id -> Ticket.id AND tenant_id -> Ticket.tenant_id
As far as a "good" use of the composite key - it depends on your design/requirements, but there is not anything "bad" about it.
“其中 id=5 的票证具有tenant_id=2”
根据该措辞（“票证”），是否只有一张 id=5 的票证？如果是这样，那就是你的主键，使用tenant_id来创建复合键只会让事情变得更加麻烦。
如果您可以有多个 id = 5，那么您可以使用复合键，是的，它需要正确匹配两者才能使引用起作用。
"where the Ticket with id=5 has tenant_id=2"
Based on that wording ("the ticket"), is there only ever one ticket with id = 5? If so, that's your primary key and using the tenant_id to make a composite key is just making things more cumbersome.
If you can have multiple id = 5 then you can use the composite key, and yes it'll need to match both correctly in order for the reference to work.