Virtual tables - Primary key

[SimonSays]

Microsoft seems to have recently made a change to the way primary keys are handled for virtual tables. I was previously able to add an external table using the SQL Server connector (connecting to a database in Azure Data Lake) that didn't have a primary key explicitly defined. I'm now no longer able to attach the same table in a different environment. I've tried adding a Guid field (generated using NEWID()) to the source view in the Data Lake, but Dataverse is not recognizing this field as a primary key. I'm not sure if I'm able to add a new field to the source table as it's schema is tightly controlled so was hoping we could go down this route. Has anybody else experienced this or have a workaround?

[AhmedSalih]

Hello, @SimonSays, Yes, Microsoft has indeed made changes to the way primary keys are handled for virtual tables in Dataverse. In order to work with virtual tables, you need to have a primary key defined in the source table.

Also, creating new view and using the GUID as NEWID(), should work:

CREATE VIEW new_view_with_pk
AS
SELECT NEWID() AS PrimaryKeyColumn, original_table.*
FROM original_table;

I would open a Microsoft support ticket to check if there is something wrong with your integration.

If my reply helped you, please give a  👍 , & if it solved your issue, please give a 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

[camille68]

I am facing the same issue. While trying to using the view in Dataverse, an error is displayed: table does not have a primary key (though there is a uniqueidentifier column). I think the problem is that a view can't have a primary key. I tried the tip from Ahmed but got the same error message.

The table doesn't have a primary key

Quick format names

ConfigurationUpdate the table name and details of the table columns before it is created in Dataverse

[SimonSays]

Thanks Camille, good to know I'm not alone with this! Unfortunately it makes Virtual Tables unusable for my needs since the source tables have too many fields for Dataverse (a separate issue I need to post about) and I have to use views instead. An ugly solution would be to create an ADF pipeline to duplicate the data to another table but I'm really uncomfortable with that. Microsoft needs to address this. 

[SimonSays]

Hi Ahmed, unfortunately I tried creating a view with a generated GUID and that didn't work either.

[camille68]

yes, I decided as a workaround to copy my view to a table and it works.

drop table xtable

SELECT *
INTO xtable
FROM xview
ALTER TABLE xtable
ADD CONSTRAINT PK_x PRIMARY KEY (xtable.xkey);

Tomorrow, I will try to schedule it on a regular basis using SSIS. Not sure dataverse will accept these regular table drops but let's try it.

Looks similar to your ADF pipeline but I'm half on-premises...

[acalder]

FYI this solution does not work.

[bgad]

Same issue here. Cannot use Views anymore as a result. 

Massive oversight by the devs. Hopefully they fix it soon.

[Tommy-Upton]

As others have said, the workaround doesn't seem to work anymore.  If anyone finds a solution, please let us know.  This is a major hindrance to adoption.

[SimonSays]

I've paused our use of Virtual Tables as a consequence of this and unfortunately it's severely curtailed the capabilities of a Power App that was built with connectivity to our Data Lake as a core feature. It would be nice if Microsoft focused on finishing things before chasing the next shiny new (AI) toy.