Traditional (SQL-like) relationships

[MarkWill]

I am pretty familiar with SQL databases and, to some extent, that is my "sweet spot", in terms of the data-driven apps I create. I am taking a serious look at the Dataverse for my business. I am excited about the possibility of one, universally acceptable resource I can leverage across many use cases. However, with the understanding that comparing SQL with Dataverse isn't an apples-to-apples comparison, there are some basic requirements that have not been as easy to figure as I would have assumed.

The most pressing is the apparently simple requirements to join tables. I understand how Dataverse implements relationships and see the value in that. But, as far as I can tell, the relationship between tables is handled through row ID references under the covers. That's all good and I see how that can be helpful. But what about more traditional primary/foreign key relationships? Let me provide an example.

I have a Products table and let's say the primary key is SKU. I also have a Products_Info table, where the primary key is SKU. I want to be able to join in the same way something like this would work.

select * from product, product_info where product.sku = product_info.sku

Pretty simple stuff in the SQL world. But how would I model this in Dataverse? If the data was totally created from scratch I can see a simple relationship (lookup column). But that's not my real-world scenario. I already HAVE these tables (from external sources), so the data is in place and therefore don't create relationships as rows are created/edited. In fact, in SQL-world I'd likely define a view for this so that I can centralize access to row sets and apps can just leverage that (as opposed to define the join in their own logic).

I am very sure I am missing something very simple here, since the ability to join tables is obviously fundamental. But I can figure out how to model and USE these type of relationships, where the join is defined by pre-existing data, rather than through Dataverse managed row ID references.

Can someone please put me out of my misery?

[jaeiow]

You would use a dataflow and populate the alternate key field. Prior to this, make your table and set up the relationship.

[MarkWill]

Ah - I see. I THINK I see what you are saying. Let me clarify (and add some extra questions :)).

I can define a key on product_info.sku, which will act as the primary key for this table, rather than the system-created GUID.

BTW, I assume I can have multiple keys, so it's not clear to me which is the primary key. How do I specify that a new key is the primary key?

Then, I can create a relationship (lookup column) from the Product table to the product_info. So far that makes sense.

But I am hoping that I don't need to re-import my data (presumably in the Product table) to establish these relationships. Is there a way to - without reimporting data - effectively, say "use column A, that already exists in the Product table, as the foreign key to reference the primary key in product_info"?

Thank you again.

[jaeiow]

I'm not sure if I can be a little more help. I do think there is a lot of time spent on this process and it is being improved. Whether you can use existing imported data, I'm thinking, no, you can't, but I might be wrong. I'm interested in hearing from others.

[Fubar]

Please note, setting an alternate key does not replace the Primary Key (GUID) i.e. all Lookups (Foreign Key relationships) are on the GIUD this is not able to be changed.  The benefit of the Alternate key is that it enforces a Unique constraint on fields that are not the GUID. 

[MarkWill]

Thank you, @Fubar. So, if I understand you correctly, the alternate key is not being used as a foreign key, at least in the traditional (SQL) sense. That would seem to bring me back to square one.

Is it not possible to create relations, then, with data that already exists in imported tables (as in my example in the opening post)? Having to artificially create and populate a Lookup column for this purpose, when the primary and foreign keys already exist in each table seems unfortunate.

Thank you again.

PS : As a side question, can I also assume that creating an alternative key also offers performance benefits i.e. as an index (or is that handled simply by enabling Searchable on the relevant column)?

[Fubar]

Yes, alternate keys are indexed. 

Also, it used to be that Fields that are added to an entities Quick Find View as Find Fields were also indexed but only the first 10 or so (can't remember the actual number).  However, if using the newer DataVerse search this is now diffenent and is limited to 950 new ones across all entities https://docs.microsoft.com/en-us/power-platform/admin/configure-relevance-search-organization#select...

For more info on dataverse alt keys see   https://docs.microsoft.com/en-us/power-apps/developer/data-platform/define-alternate-keys-entity

With imports what you can do is change the default mapping for Lookups.  This allows you to put another value in the (Lookup) column in your Child import file where that value is a unique identifier on the Master (by default it will use the name or guid, but you can set it to another field on the master).   Depending on your amount of data you currently have you may be able to add the Lookup field to the table, include it in a view and the other field that holds your existing Fkey value and then export out to Excel (i.e. export file has empty lookup column, and another column with the other field that holds the value that represented your foreign key ).  In the exported Excel file, copy your other value into the empty lookup column.  then when importing this file change the field mapping to say the map the Lookup column to field 'xyz' on the master. 

(also note: files exported to Excel the internal guid of the record is in Column A, but the first 3 columns are hidden)