Issue with SQL Azure regarding clustered index and delete constraints

Question

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.

Devart · Accepted Answer · 2013-06-13 13:06:17Z

1

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]

answered Jun 13, 2013 at 13:06

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gautam Over a year ago

Adding the CONSTRAINT with ON [PRIMARY] results in Msg 40514, Level 16, State 1, Line 23 'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server. Removing ON PRIMARY works but will this still support delete cascades?

Devart Over a year ago

Sorry, but I didn't mean that. I just proposed add a composite PK.

Gautam Over a year ago

My apologies. I guess you meant to say that include the Primary Key constraint on FileCategory (in addition to the Foreign Key constraint) Having both constraints worked. Thanks,

Collectives™ on Stack Overflow

Issue with SQL Azure regarding clustered index and delete constraints

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related