SQL:多个表需要自动增量主键吗? [英] SQL: Do you need an auto-incremental primary key for Many-Many tables?
问题描述
ArtistFans
ArtistFanID(PK)
ArtistID(FK)
UserID(FK)
(ArtistID和UserID将被禁用唯一约束
以防止重复的数据)
或者您是否为两个相关字段构建复合PK:
ArtistFans
ArtistID(PK)
UserID(PK)
(由于
复合PK)
有没有
ArtistFans
ArtistID(PK)
UserID(PK)
使用自动增量PK这里没有优势,即使父表有它们。
我也会在(UserID,ArtistID)
上自动创建一个反向PK索引:你将需要它,因为您将通过两列查询表。
自动编号/ ID列具有它们的位置。您可以根据物理平台选择他们在规范化过程之后改进某些事情。但是不是链接表:如果你的脑子里的ORM坚持,然后改变ORM ...
编辑,2012年10月
请注意,您仍然需要唯一的(UserID,ArtistID)
和(ArtistID,UserID)
索引。添加自动增量仅使用不应该使用的更多空间(在内存中,而不仅仅是磁盘上)
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)
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)
The use of an auto incremental PK has no advantages here, even if the parent tables have them.
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.
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...
Edit, Oct 2012
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屋!