Forum Discussion

esobrino's avatar
esobrino
Helper II
2 years ago

Data Warehouse - SQL Database Projects Issues + 'ALTER TABLE' are not supported in Fabric

Here are some little issues:   • Selecting a Profile always revert to an “SQL Login” Authentication type … “Microsoft Entra ID – Universal with MFA support” is expected. • A batch should not only ...
data warehouse
AndyDDC's avatar
AndyDDC
Most Valuable Professional
2 years ago

What ALTER statements are you running manually?

esobrino's avatar
esobrino
Helper II
2 years ago

Here is a partial sample of a typical successfully tested table definition within the "SQL Database Project" in "Azure Data Studio":

 

CREATE TABLE [Case].[CaseDetail] (
   [CaseDetailNo] INT NOT NULL,
   [CaseID] VARCHAR(20) NOT NULL,
   [CaseNumber] VARCHAR(20) NOT NULL,
   [TypeID] VARCHAR(20),
   [CaseTitle] VARCHAR(128),
   [FiledDate] DATE,
   [JudgeNo] INT,
   [CourtNo] INT
);
GO

 

ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT pk_CaseDetail_No
PRIMARY KEY NONCLUSTERED (CaseDetailNo) NOT ENFORCED;
GO

 

ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Type FOREIGN KEY(TypeID)
REFERENCES [Case].[CaseType](TypeID) NOT ENFORCED;
GO


ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Court FOREIGN KEY(CourtNo)
REFERENCES [Entity].[Organization](OrganizationNo) NOT ENFORCED;
GO


ALTER TABLE [Case].[CaseDetail] ADD CONSTRAINT fk_CaseDetail_Officer FOREIGN KEY(JudgeNo) REFERENCES [Entity].[Officer](OfficerNo) NOT ENFORCED;
GO

 

Normally, when all works as expected and when you "Publish" the database it creates all objects.  Those existing objects will be dropped...  (or depends on how you set-it-up within the advance options).

 

What I expect is that "publish" behaves the same as the similar VS "Database Project" that has been available for years...  and if it runs manually it must run through "dapac".  The ALTER TABLE limitations are OK and those declared above follow the documentation and as explained all work well in the "Data Warehouse" and manually in "Azure Data Studio"...

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi esobrino 
    Thanks for using Fabric Community.
    The internal team replied as follows:

    We have an artificial block on all alter table operations right now. You raise a good point that constraints can be added using alter table but unfortunately since we've implemented this block we cannot add constraints at this time through SQL projects. This block will be lifted in the coming semester.

    Apart from this I could not understand this point from the first question:

    A batch should not only be allowed to have one statement on scripts. For example, it is possible that some variables may be needed if you are trying to populate a table. Alternatively, allow the script not to be executed (is not clear how to do this). In VS the Database Projects allow for such…

    Can you please explain this?

    Thanks