rails-sqlserver/activerecord-sqlserver-adapter — Issue #166: Error Setting Primary Key through Migration
Author: jazzdan
Created: 2012-02-21 23:21:36+00:00
State: open

I ran across an error today migrating my SQL Server 2008 R2 database. The migration in question looks like this:

```ruby
class MakeHistoryIdPrimaryKey < ActiveRecord::Migration
def change
change_column :history_records, :history_id, :primary_key
end
end
```

Here is my gemfile for good measure:

```ruby
source 'http://rubygems.org'

gem 'rails', '3.1.1'

# Bundle edge Rails instead:
# gem 'rails', :git => 'git://github.com/rails/rails.git'

gem 'sqlite3', '1.3.4' #for *nix c ruby
gem 'tiny_tds', '0.5.1' #for MSSQL
gem 'activerecord-sqlserver-adapter' #for MSSQL
gem 'devise'
gem 'cancan' # see this link for implementation https://github.com/ryanb/cancan/wiki/Defining-Abilities
gem 'aasm'
gem 'acts_as_audited', '2.0.0.rc7'
gem 'sunspot_rails'
gem 'paperclip', '~> 2.3'
gem 'chunky_png'
gem 'capistrano'

group :assets do
# gem 'handlebars_assets'
gem 'uglifier'
end

gem 'jquery-rails'
gem 'therubyracer'
gem 'devise_cas_authenticatable', :git => 'git://github.com/nbudin/devise_cas_authenticatable.git'
#gem 'devise_cas_authenticatable'
# Use unicorn as the web server
gem 'unicorn'
gem 'newrelic_rpm'
```

And here is the output when I try to run the migration:

```bash
$ rake db:migrate RAILS_ENV=test --trace
(in /var/www/minerva/releases/20120217171102)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
New Relic Agent not running.
** Invoke db:load_config (first_time)
** Invoke rails_env (first_time)
** Execute rails_env
** Execute db:load_config
** Execute db:migrate
EXECUTE (0.8ms) USE [minerva_test]
(1.3ms) SELECT [schema_migrations].[version] FROM [schema_migrations]
Migrating to DeviseCreateUsers (20110728020923)
Migrating to CreateBuildings (20110729022442)
Migrating to CreateMachineTypes (20110729030133)
Migrating to CreatePrimaryUses (20110729030252)
Migrating to CreateInventoryRecords (20110807194952)
Migrating to AddAdminToUser (20110807230446)
Migrating to RenameStatusToState (20110808161244)
Migrating to CreateAuditTable (20110808164753)
Migrating to InstallActsAsAudited (20110808170141)
Migrating to CreateDeployments (20110818141843)
Migrating to AddSignatureToDeployments (20110819130833)
Migrating to AddCsrToDeployments (20110819163405)
Migrating to AddSignatureMetadataToDeployments (20110819165305)
Migrating to FixSignatureColumnName (20110819165447)
Migrating to AddAvatarColumnsToUser (20110823184028)
Migrating to ChangeSignatureAvatarUpdatedAtToSignatureUpdatedAt (20110830221214)
Migrating to ChangeDeploymentsToEvents (20110830235817)
Migrating to CompleteInventoryRecordSchema (20110913225644)
Migrating to RemoveEvents (20111003152152)
Migrating to CreateEvents (20111003154501)
Migrating to CreateEventActions (20111003155304)
Migrating to FixRecordId (20111003161029)
Migrating to ChangeEventActionsToEventCategorizations (20111004223609)
Migrating to EffingTypoInSchema (20111004231332)
Migrating to CreateHistoryRecords (20111019163447)
Migrating to LowerCaseInventoryRecordsFields (20111019165014)
Migrating to RemoveAdminColumnFromUserTable (20111020181433)
Migrating to AddCasAuthenticatableToUsers (20111027152809)
Migrating to RenameEncryptedPasswordToPassword (20111027185500)
Migrating to DropUsersTable (20111027193453)
Migrating to AddCasUsersTable (20111027195115)
Migrating to AddDeviseTraitsToUsersTable (20111027204955)
Migrating to AddEmailColumnToUsersTable (20111027210107)
Migrating to AddIndexToInventoryRecords (20111103180558)
Migrating to AddRoleToUsers (20111104201009)
Migrating to ConformWithPaulsColumnNames (20111202200923)
Migrating to AddOrderIdToInventoryRecords (20111202201556)
Migrating to RevertConformWithPaulsColumnNames (20111202204909)
Migrating to SetDefaultRole (20111230062021)
Migrating to CreateImages (20120105214814)
Migrating to ConsolidateLastNameAndFirstNametoPerson (20120201012224)
Migrating to AddDescriptionToImages (20120201014447)
Migrating to AddUserMetadata (20120201014834)
Migrating to ConsolidateLastNameAndFirstNameToPersonInHistory (20120203144825)
Migrating to MakeHistoryIdPrimaryKey (20120203193431)
EXECUTE (0.7ms) BEGIN TRANSACTION
== MakeHistoryIdPrimaryKey: migrating ========================================
-- change_column(:history_records, :history_id, :primary_key)
(4.3ms) EXEC sp_executesql N'EXEC sp_helpconstraint ''history_records'',''nomsg'''
EXECUTE (1.3ms) ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
TinyTds::Error: Incorrect syntax near the keyword 'IDENTITY'.: ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
EXECUTE (2.1ms) IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
rake aborted!
An error has occurred, this and all later migrations canceled:

TinyTds::Error: Incorrect syntax near the keyword 'IDENTITY'.: ALTER TABLE [history_records] ALTER COLUMN [history_id] int NOT NULL IDENTITY(1,1) PRIMARY KEY
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:354:in `do'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:354:in `raw_connection_do'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:320:in `block (2 levels) in do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:485:in `with_sqlserver_error_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:320:in `block in do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:244:in `block in log'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activesupport-3.1.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract_adapter.rb:239:in `log'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:319:in `do_execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `block in change_column'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:91:in `change_column'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:447:in `block in method_missing'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:422:in `block in say_with_time'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/benchmark.rb:295:in `measure'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:422:in `say_with_time'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:442:in `method_missing'
/var/www/minerva/releases/20120217171102/db/migrate/20120203193431_make_history_id_primary_key.rb:3:in `change'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:391:in `block (2 levels) in migrate'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/benchmark.rb:295:in `measure'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:391:in `block in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:185:in `with_connection'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:375:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:507:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:687:in `block (2 levels) in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `call'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `block in ddl_transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:52:in `block in transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/core_ext/database_statements.rb:37:in `transaction_with_retry_deadlock_victim'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-sqlserver-adapter-3.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:52:in `transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/transactions.rb:208:in `transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:742:in `ddl_transaction'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:686:in `block in migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:671:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:671:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:549:in `up'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/migration.rb:530:in `migrate'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.1.1/lib/active_record/railties/databases.rake:161:in `block (2 levels) in '
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `call'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:205:in `block in execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:200:in `execute'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:158:in `block in invoke_with_call_chain'
/home/deployer/.rvm/rubies/ruby-1.9.2-p290/lib/ruby/1.9.1/monitor.rb:201:in `mon_synchronize'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:151:in `invoke_with_call_chain'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/task.rb:144:in `invoke'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:116:in `invoke_task'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block (2 levels) in top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `each'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:94:in `block in top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:88:in `top_level'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:66:in `block in run'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:133:in `standard_exception_handling'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/lib/rake/application.rb:63:in `run'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/gems/rake-0.9.2.2/bin/rake:33:in `'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/bin/rake:19:in `load'
/home/deployer/.rvm/gems/ruby-1.9.2-p290/bin/rake:19:in `

'
Tasks: TOP => db:migrate
```

Let me know if there is any other information I can provide that would be helpful. Thank you for all your hard work and effort!