SQL:你需要一个多对多表的自动增量主键吗? [英] SQL: Do you need an auto-incremental primary key for Many-Many tables?

问题描述

假设您在艺术家和粉丝之间有一张多对多表.说到设计桌子，你是不是这样设计桌子的:

Say you have a Many-Many table between Artists and Fans. When it comes to designing the table, do you design the table like such:

ArtistFans
    ArtistFanID (PK)
    ArtistID (FK)
    UserID (FK)

 (ArtistID and UserID will then be contrained with a Unique Constraint 
  to prevent duplicate data) 

或者您是否为两个相关字段构建使用复合 PK:

Or do you build use a compound PK for the two relevant fields:

ArtistFans
    ArtistID (PK)
    UserID (PK)

(The need for the separate unique constraint is removed because of the 
 compound PK)

<小时>

使用以前的模式有什么优势(也许是索引?)?

---

Are there are any advantages (maybe indexing?) for using the former schema?

推荐答案

ArtistFans
    ArtistID (PK)
    UserID (PK)

使用自动增量 PK 在这里没有优势，即使父表有它们.

The use of an auto incremental PK has no advantages here, even if the parent tables have them.

我也会在 (UserID, ArtistID) 上自动创建一个反向 PK"索引:您将需要它，因为您将按两列查询表.

I'd also create a "reverse PK" index automatically on (UserID, ArtistID) too: you will need it because you'll query the table by both columns.

自动编号/ID 列有其位置.在基于物理平台的规范化过程之后，您会选择它们来改进某些事情.但不适用于链接表:如果你脑残的 ORM 坚持，那么改变 ORM...

Autonumber/ID columns have their place. You'd choose them to improve certain things after the normalisation process based on the physical platform. But not for link tables: if your braindead ORM insists, then change ORMs...

编辑，2012 年 10 月

Edit, Oct 2012

请务必注意，您仍然需要唯一的 (UserID, ArtistID) 和 (ArtistID, UserID) 索引.添加自动增量只会使用更多不应使用的空间(在内存中，而不仅仅是在磁盘上)

It's important to note that you'd still need unique (UserID, ArtistID) and (ArtistID, UserID) indexes. Adding an auto increments just uses more space (in memory, not just on disk) that shouldn't be used

这篇关于SQL:你需要一个多对多表的自动增量主键吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！