CODEWITHSUNDEEP
sqlforeign-keys
somestack
somestack

Reputation: 13

Is that many-to-many sql relationship?

I have two tables: 

A (id, name) 
B (idA1, idA2, ...)

Combination of idA1 and idA2 is a primary key. At the same time idA1 and idA2 are foreign keys to tableA.

Is that many-to-many relationship?

Upvotes: 1

Views: 37

Answers (2)

GMB
GMB

Reputation: 222462

This is a many-to-many relationship, with a specificity is that both the referencing and referenced tables are the same.

Basically, each record in tableA may refer to 0, 1 or several records in the same table, through bridge table tableB.

One comparison that comes to mind is a representation of a tree, where tableA stores the nodes and tableB is a closure table, that stores all possible hierarchy paths (as explained for example in this SO question).

nodes table:

id    name
1     foo
2     bar
3     baz
4     zoo

paths table:

parent    child
1         1
1         2
1         3
1         4
2         3
2         4

Which represents a tree like:

  1
  |
  2
 / \
3   4

Upvotes: 0

Erwin Smout
Erwin Smout

Reputation: 18408

Yes. With the info provided, there's nothing there to prevent multiple distinct idA2 values appearing in B combined with the same idA1 value, and there's also nothing in there to prevent multiple distinct idA1 values appearing in B combined with the same idA2 value. So each id appearing in A can potentially be associated with many other id values in A (through idA1 -> idA2) as well as in the other direction (through idA2 -> idA1).

Upvotes: 1

Related Questions