Issue with SQL Azure regarding clustered index and delete constraints

4
votes

I have the following SQL script that seems to work on local SQL 2008 R2 instance but fails on Azure SQL.

I have scoured the web but have not found any 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 runs fine however it fails on the following statement with the error shown 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. Please create a clustered index and try again.

1
sql-serverazureazure-sql-database

1 Answers

2
votes

Try to set PRIMARY KEY on 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]