Re-deploying a table with a primary key fails using SQL database projects in Azure data Studio

[DannyBr]

Hi,

Doing a investigation/analysis for moving our dedicated spools to fabric DW. Trying to deploy our first simple script which is a calendar table (simplified from original).

CREATE TABLE bus.calendar_master
( calendar_date date NOT NULL
)
GO

/* Create Primary Keys*/

ALTER TABLE bus.calendar_master
 ADD CONSTRAINT PK_calendar_master
	PRIMARY KEY NONCLUSTERED (calendar_date) NOT ENFORCED;
GO

Using the publish command in ADS on an empty database runs successfully and the table is created with the PK. If you then immediatly publish again (i.e. table and PK already exists) you get this error message:

Deploy dacpac: Could not deploy package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 24585, Level 16, State 6, Line 1 The specified ALTER TABLE statement is not supported in this edition of SQL Server.
Error SQL72045: Script execution error. The executed script:
ALTER TABLE [bus].[calendar_master] CHECK CONSTRAINT [PK_calendar_master];

This is because the tool generated publish script incorrectly adds the check constraint which is not support by Fabric. I have checked and re-selected (several times) the project Target platform is "Synapse Datawarehouse in Microsoft Fabic" so the target platform seems correct.

Can anyone help? Thanks.

[dsArchitect]

Hi @DannyBr ,

I’m not sure if you’ve resolved this issue yet, but I encountered the same problem. I couldn’t find a setting in Azure Data Studio to restrict validation checks for constraints. What worked for me was disabling check constraints for INSERT and UPDATE statements in my table creation scripts.

Disable check constraints with INSERT and UPDATE statements - SQL Server | Microsoft Learn

For example:

CREATE TABLE [bus].[calendar_master]
(
  [calendar_date] DATETIME2(6) NULL  
);
GO
ALTER TABLE [bus].[calendar_master] ADD CONSTRAINT PK_calendar_master PRIMARY KEY NONCLUSTERED (calendar_date) NOT ENFORCED;
GO
ALTER TABLE [bus].[calendar_master] NOCHECK CONSTRAINT PK_calendar_master;  

[DannyBr]

Thanks @dsArchitect.

No I hadn't found a solution. We have moved on becuase in our short evaluation our findings were not favourable. For our required features we do not think it is ready for a end to end SDLC without significant effort on our part. There are too many missing or incorrectly working features and poor documentation for us at this stage.  We will be staying with synapse for the time being and tactically using fabric/one-lake where adventagious. We may reconsider if/when the platform matures further.

Cheers.

[Anonymous]

Hi @DannyBr ,

The error message you are seeing is due to Synapse Data Warehouse in Microsoft Fabric not supporting certain commands.

In your case, the statement is for adding a check constraint, which is not supported. ALTER TABLE That's why you see the error message when you try to publish again.

In Microsoft Fabric's warehouse, if you need to make changes to a table you need to use, you may need to drop and recreate the table.

Here are two similar questions, check them out:

Data Warehouse - SQL Database Projects Issues + 'A... - Page 2 - Microsoft Fabric Community

Solved: Re: ALTER TABLE statement is not supported in MS F... - Microsoft Fabric Community

I hope this helps! Let me know if you have any additional questions.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[DannyBr]

Hi @Anonymous,

Yes I agree but as stated in my original post this statement is being generated by the ADS deplyment tool not my code. 

The ALTER - Add primary key in my code is a valid statement per:

https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints#examples

This runs successfully the first time. However on the second publish, with no changes, the error is presented.

The statement being generated by the ADS SQL data project in the publish script is invalid even though I have the correct target selected.

In my opinion this seems to be a fault with the ADS Data project tool or perhaps something I am not setting correctly.

The correct output from the tool should be a "no change" script.

Cheers,

Danny