开发者

On the relationship of database tables to a pivot/junction table

开发者 https://www.devze.com 2023-01-14 15:30 出处:网络
In a database, one may create a many-to-many relationship between tables by creating a third table which maps the two together using foreign keys.What is the 开发者_StackOverflowrelationship between t

In a database, one may create a many-to-many relationship between tables by creating a third table which maps the two together using foreign keys. What is the 开发者_StackOverflowrelationship between the third table and the two original tables?

For example, if table A and table B have a many-to-many relationship and table AB is the pivot table, is the A->AB relationship always a many-to-one relationship, and is the B->AB relationship also always a many-to-one relationship?


I believe the short answer is "Yes". :)

Simply put, foreign key relationships must map an AB column to a single column in A or a single column in B. But since the contents of AB represent the many-to-many relationship between A and B, there can by definition be multiple instances of either the A key or the B key in AB. At the same time, a duplicate record in AB, ie. representing the same A-B relationship multiple times, would be in error.

So, yes: one-to-many on either side of AB.


Being picky, A->AB and B->AB are one-to-many relationships, not many-to-one: an entity in A should appear once in A, but can appear many times in AB (linking it to several B instances); and an entity in B should appear once in B, but again could appear many times in AB (linking it to several A instances).

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号