CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-databasecomposite-key
Saurabh Palatkar
Saurabh Palatkar

Reputation: 3384

Unable to alter the Composite Primary Key in Azure Sql

Table Definition:

CREATE TABLE [dbo].[tbl](
    [Id1] [int] NOT NULL,
    [Id2] [int] NOT NULL,
    [Id3] [int] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [CreatedTs] [datetime] NOT NULL,
 CONSTRAINT [PK_tbl] PRIMARY KEY CLUSTERED 
(
    [Id1] ASC,
    [Id2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [dbo].[tbl] ADD  CONSTRAINT [DF_tbl_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[tbl] ADD  CONSTRAINT [DF_tbl_CreatedTs]  DEFAULT (getdate()) FOR [CreatedTs]
GO

In above table the I've composite primary key using "Id1" and "Id2" combination. Now I want to include "Id3" in composite primary key, for that I am doing following:

ALTER TABLE tbl
DROP CONSTRAINT PK_tbl


ALTER TABLE [dbo].[tbl] ADD  CONSTRAINT [PK_tbl] PRIMARY KEY CLUSTERED 
(
    [Id1] ASC,
    [Id2] ASC,
    [Id3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
GO

The above query runs perfectly fine on my local sql server db, but when I run it on Azure db I get error:

Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again.

How should I modify the composite primary key on azure sql?

Upvotes: 0

Views: 1048

Answers (3)

Umachandar - Microsoft
Umachandar - Microsoft

Reputation: 484

The operations you are doing is supported on old & V12 versions of SQL Database Servers. Clustered index is a requirement only for inserts to happen on non-V12 servers. You can create a heap fine in non-V12 database or drop & recreate the clustered index/constraint. So you should not get this error. How are you running these statements? And what tool are you using to run the statements?

Upvotes: 1

tmullaney
tmullaney

Reputation: 1745

Azure SQL Database's latest update (V12) allows you to have tables without clustered indexes (i.e. as heaps). If you upgrade your server to the latest version, you'll be able to run your queries to modify the PK successfully.

Other features enabled by V12: http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-whats-new/

How to upgrade: http://azure.microsoft.com/en-us/documentation/articles/sql-database-preview-upgrade/

Upvotes: 6

dean
dean

Reputation: 10098

The problem is that dropping the PK constraint also drops the underlying clustered index, and heaps are not permitted in Azure.

The best you can do is to create a new table with desired structure, copy the data over, drop the old table, rename the new one and recreate the FKs if any.

Upvotes: 1

Related Questions