SQL Azure issue regarding clustered index restrictions and deletion

Question

I have the following SQL script that seems to work on a local instance of SQL 2008 R2, but does not work on Azure SQL.

I browsed the web but haven’t found a solution yet.

Any suggestions?

I would like to avoid Identity columns.

CREATE TABLE dbo.[Category] ( CategoryId NVARCHAR(36), CONSTRAINT PK_Category_CategoryId PRIMARY KEY CLUSTERED(CategoryId) ) GO CREATE TABLE dbo.[File] ( FileId NVARCHAR(36), CONSTRAINT PK_File_FileId PRIMARY KEY CLUSTERED(FileId) ) GO CREATE TABLE dbo.[FileCategory] ( FileId NVARCHAR(36), CategoryId NVARCHAR(36) CONSTRAINT FK_FileCategory_FileId FOREIGN KEY (FileId) REFERENCES [File](FileId) ON DELETE CASCADE, CONSTRAINT FK_FileCategory_CategoryId FOREIGN KEY (CategoryId) REFERENCES [Category](CategoryId) ON DELETE CASCADE, ) GO INSERT INTO [Category] VALUES('ABC') INSERT INTO [Category] VALUES('DEF') INSERT INTO [Category] VALUES('GHI') GO 

The above steps work fine, however, in the following expression, this fails with the error below:

 DELETE FROM [Category] WHERE [CategoryId] = 'ABC' 

Msg 40054, Level 16, State 1, Line 3 Tables without a clustered index are not supported in this version of SQL Server. Create a clustered index and try again.

Answer 1

Try setting PRIMARY KEY to FileCategory -

 CREATE TABLE [dbo].[FileCategory] ( [FileId] [nvarchar](36) NOT NULL, [CategoryId] [nvarchar](36) NOT NULL, CONSTRAINT [PK_FileCategory] PRIMARY KEY CLUSTERED ( [FileId], [CategoryId] ) ON [PRIMARY] ) ON [PRIMARY]