Change primary key when using EF migrations in Sql Azure

Question

I have changed the primary key in my entity. When I try and run the migration on SQl Azure it fails with "Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again."

 public override void Up()
 {
    DropPrimaryKey("User", new[] { "Id" });
    AddPrimaryKey("User", "Username");
 }

How can I solve this? The only thing I can think of is to create my own ChangePrimaryKey method that somehow silently creates a temporary table with the correct primary key, copies the data over, then replaces the original.

AvkashChauhan · Answer 1 · 2012-06-18T16:23:59.287

1

Tables without a clustered index are not supported in SQL Azure. That means you can't create new tables without cluestered indexes, and you cannot drop the existing clustered index on an existing table. Windows Azure requires a clustered index on every table.

As per Cihan Biyikoglu (MSFT) one workaround is to create a new table with the index structure you like, move data over and rename the tables in a single transaction.

With Stored Procedure you can do like as below:

begin
 tran
 exec
 sp_rename 'db1','db1_old'
 exec
 sp_rename 'db1_new','db1'
 commit
tran

edited Jun 18 '12 at 16:23

answered Jun 18 '12 at 16:02

AvkashChauhan

20,495
3
34
65

1

That is the horrible conclusion I have come to. It kind of means EF migrations are broken if you use SQL Azure. Hopefully they can do something to sort it out. Now I am writing my own stored proc that will do the job (non-trivial definitely). – Ian1971 Jun 18 '12 at 16:05
I do agree with your statement and your feedback is shared. I have added more info, as you could use SP. Thanks!! – AvkashChauhan Jun 18 '12 at 16:25

score 1 · Accepted Answer · edited May 23 '17 at 10:24

Based on workaround suggested by Avkash. And getting some ideas from https://stackoverflow.com/a/317864/53970 I have come up with a stored proc that will change the primary key on a table. There are probably some issues with some table structures (works ok for me so far) and it doesn't handle triggers. But it does copy structure, data, constraints (primary, foreign, check) and indexes. Posting it here in case someone else runs into the same brick wall I did.

create procedure dbo.ChangePK
    @src sysname,
    @pklist nvarchar(4000), --comma list of primary key fields
    @skipfinalrename bit = 1 --set to not perform anything destructive on src table or related tables (for testing)
as

set nocount on

declare @tmpPrefix nvarchar(10)
set @tmpPrefix = 'tmp_'

declare @dest sysname
set @dest = 'tmpCopy'

declare @sql nvarchar(max)
set @sql = ''
--create table script
select @sql = @sql + ' IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''' + @dest + ''')  AND OBJECTPROPERTY(id, N''IsTable'') = 1) drop table [' + @dest + ']; create table [' + @dest + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + @dest + ' ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + @pklist + ')' END + ';'
from    sysobjects so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
                when 'sql_variant' then ''
                when 'text' then ''
                when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
                else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        'IDENTITY(' + 
        cast(ident_seed(so.name) as varchar) + ',' + 
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
cross apply
    (SELECT 
        ',  ['+column_name+'] ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) c (columnlist)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')
and name = @src

print 'create'
print @sql
exec sp_executesql @sql

--now the  inserts
set @sql = ''
select @sql = @sql + ' set identity_insert [' + @dest + '] on; insert into [' + @dest + '] (' + STUFF(c.columnlist,1,2,'') + ') select ' + STUFF(c.columnlist,1,2,'') + ' from [' + @src + '] ; set identity_insert [' + @dest + '] off;'
from    sysobjects so
cross apply
    (SELECT 
        ',  ['+column_name+'] ' 

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) c (columnlist)
left join
    information_schema.table_constraints tc
on  tc.Table_name               = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM       information_schema.key_column_usage kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name        NOT IN ('dtproperties')
and name = @src

print 'data'
print @sql
exec sp_executesql @sql

--now the foreign keys
set @sql = ''
select @sql =  @sql + case when tc.Constraint_Name is null then '--no foreign keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + rctc.table_name + '] (' + STUFF(fk2.list,1,2,'') + ');' end
from    sysobjects so
left join
    information_schema.table_constraints tc on  tc.Table_name = so.Name 
                                AND tc.Constraint_Type  = 'FOREIGN KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcc on tc.constraint_name = rcc.constraint_name
left join INFORMATION_SCHEMA.table_constraints rctc on rcc.unique_constraint_name = rctc.constraint_name
cross apply
    (select ', [' + Column_Name + ']'
     FROM       INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) fk1 (list)
cross apply
    (select ', [' + kcu.Column_Name + ']'
     FROM      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
     JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc.unique_constraint_name = kcu.constraint_name
     WHERE      rc.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) fk2 (list)

where   xtype = 'U'
and name = @src

print 'foreign keys'
print @sql
exec sp_executesql @sql

--now the unique keys
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no unique keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' UNIQUE NONCLUSTERED ' + ' (' + STUFF(fk1.list,1,2,'') + ');' end
from    sysobjects so
left join
    information_schema.table_constraints tc on  tc.Table_name = so.Name 
                                AND tc.Constraint_Type  = 'UNIQUE'
cross apply
    (select ', [' + Column_Name + ']'
     FROM       INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     WHERE      kcu.Constraint_Name     = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) fk1 (list)

where   xtype = 'U'
and name = @src

print 'unique keys'
print @sql
exec sp_executesql @sql


--now check constraints
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no check constraints' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' CHECK ' + ' (' + cc.check_clause + ');' end
from    sysobjects so
left join
    information_schema.table_constraints tc on  tc.Table_name = so.Name 
                                AND tc.Constraint_Type  = 'CHECK'
left join INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc on      cc.Constraint_Name     = tc.Constraint_Name

where   xtype = 'U'
and name = @src

print 'check constraints'
print @sql
exec sp_executesql @sql

if (@skipfinalrename = 1)
    return

set xact_abort on

--now we start affecting the src table
begin tran
--drop fk constraints on src referencing current primary key
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE ' + rctc.table_name + ' DROP CONSTRAINT ' + rc.Constraint_Name + ';' end
from    sysobjects so
left join information_schema.table_constraints tc on  tc.Table_name = so.Name  
                    AND tc.constraint_type  = 'PRIMARY KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name
left join information_schema.table_constraints rctc on  rc.constraint_name = rctc.constraint_name  
where   xtype = 'U'
and name = @src  

--create fk constraints on dest referencing new primary key
declare @sql2 nvarchar(max)
set @sql2 = ''
select @sql2 = @sql2 + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE [' + rctc.table_name + '] WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + rc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + @dest + '] (' + STUFF(fk2.list,1,2,'') + ');' end
from    sysobjects so
left join information_schema.table_constraints tc on  tc.Table_name = so.Name  
                    AND tc.constraint_type  = 'PRIMARY KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name 
left join information_schema.table_constraints rctc on  rc.constraint_name = rctc.constraint_name  
cross apply
    (select ', [' + Column_Name + ']'
     FROM       INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
     WHERE      kcu.Constraint_Name     = rc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) fk1 (list)
cross apply
    (select ', [' + kcu.Column_Name + ']'
     FROM      INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
     JOIN      INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc1.unique_constraint_name = kcu.constraint_name
     WHERE      rc1.Constraint_Name     = rc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) fk2 (list)
where   xtype = 'U'
and name = @src  


----

print 'create new ref fk'
print @sql2
exec sp_executesql @sql2

print 'drop original ref fk'
print @sql
exec sp_executesql @sql

--now we can create the index sql
set @sql2 = ''
select @sql2 = @sql2 + ' IF  NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @src + ''') AND name = N''' + i.name + ''') 
CREATE ' + i.type_desc COLLATE Latin1_General_CS_AS + ' INDEX ' + i.name + ' ON [' + @src + '] (
    ' +  STUFF(ix.list,1,2,'') + '
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY];
'
from sys.tables as t 
inner join sys.indexes as i on t.[object_id] = i.[object_id] 
cross apply
    (select ', [' + ac.name + ']'
     FROM       sys.index_columns ic
     inner join sys.all_columns as ac on ic.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id] 
     WHERE      ic.[object_id] = i.[object_id] and ic.[index_id] = I.[index_id]
     ORDER BY
        key_ordinal
     FOR XML PATH('')) ix (list)
where 
t.name = @src

--now drop the original table
set @sql = 'drop table [' + @src + '];'
print 'drop original original'
print @sql
exec sp_executesql @sql

--now rename the constraints (remove leading tmp_ in effect)
set @sql=''
select @sql = @sql + ' exec sp_rename ''' + tc.constraint_name + ''', ''' + substring(tc.constraint_name, 5, len(tc.constraint_name)-4) + ''';'
from    sysobjects so
left join
    information_schema.table_constraints tc on  tc.Table_name = so.Name 
where   xtype = 'U'
and name = @dest  

print 'rename constraints to original names'
print @sql
exec sp_executesql @sql

--now rename the table back to the original
exec sp_rename @dest, @src

print 'finally apply the indexes'
print @sql2
exec sp_executesql @sql2

print 'finished' 

commit tran

Change primary key when using EF migrations in Sql Azure

2 Answers2