Answered

Dataverse: Many-To-Many join table

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Dear Microsoft Dataverse users,

we are working on a canvas app handling customer information. Our tables are "Customer" and "Product/Service". These tables have a many to many relationship and are therefore connected by a joined table. Coming from an SQL standpoint, the joined table should have a primary key, containing both customerFK aswell as the Product/ServiceFK. This table should be named "CustomerProducts".

So far, I have not found the option to create a composite primary key, consisting of both FKs. Any suggestions? Our alternative would be to create a new primary key.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Parvez Ghumra 1,579 Moderator on at

Like (0)

Report
Copy link

Link copied!

@22Faruk If you have modelled the relationship between both tables as a genuine many-to-many relationship, then the platform has created the intersect table for you automatically. However, this table is hidden from the maker portal's UI and you cannot customise it. The platform manages the data in this table for you automatically as you relate/unrelate records across the two tables on this relationship. And the constraint of only allowing unique combinations of records in this intersect table is also managed for you automatically by the platform. You do not need to implement any key constraints to enforce this.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@parvezghumra ty for your reply. Well, I forgot to mention that in our context, we want to add metadata to this hidden intersect table. Do you have any other advice for our problem? A way to keep a many-to-many and configure the intersect table as well?

Was this reply helpful? Yes No
Verified answer

Parvez Ghumra 1,579 Moderator on at

Like (1)

Report
Copy link

Link copied!

@22Faruk If you need to capture additional data on the intersect table, then your many-to-many relationship needs to be replaced/decomposed by introducing a custom intersect table between your two existing tables using a one-to-many and a many-to-one relationship. You can then add additional columns on this custom intersect table. Your ERD should look similar to the following

Customer ------<-Customer Product/Service ->----- Product/Service

You will then be able to add a Key to the custom intersect table to enforce uniqueness of values on specific column combinations

Was this reply helpful? Yes No
Fubar 8,487 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Answer provided by Parvez is correct, just adding, you may also see it referred to as a Manual (or custom) many-to -many versus the native or out of the box many-to-many

Was this reply helpful? Yes No