Geek Answers Handbook

Primary key change when using EF migrations in Azure Sql

I have changed the primary key in my entity. When I try to migrate to Azure SQl, it fails: "Tables without a clustered index are not supported in this version of SQL Server. 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, which somehow creates a temporary table with the correct primary key, copies the data and replaces the original.

+4
source share
2 answers

Based on the workaround suggested by Avvas. And get some ideas from fooobar.com/questions/82030 / ... I came up with a saved process that will change the primary key in the table. There are probably some problems with some table structures (works for me so far) and it does not handle triggers. But it copies structure, data, constraints (primary, external, checks) and indexes. Passing it here if someone else collides with the same brick wall as me.

 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
+1
source

Tables without a clustered index are not supported in SQL Azure. This means that you cannot create new tables without indexed indexes, and you cannot delete an existing clustered index in an existing table. Windows Azure requires a clustered index for each table.

According to Cihan Biyikoglu (MSFT), one way is to create a new table with the necessary index structure, move data, and rename tables in one transaction.

With the saved procedure, you can do as shown below:

 begin tran exec sp_rename 'db1','db1_old' exec sp_rename 'db1_new','db1' commit tran
+1
source

More articles:


All Articles