What steps will reproduce the problem? $ cat migration.rb require 'sequel' require 'logger'

Sequel.extension :migration

DB = Sequel.connect 'jdbc:sqlserver://localhost;database=test;user=test;password=test'

DB.loggers << Logger.new($stdout)

Class.new(Sequel::Migration) do def up create_table :comments do primary_key :id end end end.apply(DB, :up)

posts = Class.new(Sequel::Migration) do def up create_table :posts do primary_key :id foreign_key :comment_id, :table => :comments end end

def down alter_table :posts do drop_column :comment_id end end end

posts.apply(DB, :up) posts.apply(DB, :down)

What is the expected output? Successful FK column drop

What do you see instead? $ jruby migration.rb I, [2010-02-03T08:26:08.859000 #5676] INFO -- : CREATE TABLE [COMMENTS] ([ID] integer PRIMARY KEY IDENTITY(1,1)) I, [2010-02-03T08:26:09.266000 #5676] INFO -- : CREATE TABLE [POSTS] ([ID] integer PRIMARY KEY IDENTITY(1,1), [COMMENT_ID] integer REFERENCES [COMMENTS]) I, [2010-02-03T08:26:09.266000 #5676] INFO -- : ALTER TABLE [POSTS] DROP COLUMN [COMMENT_ID] com/microsoft/sqlserver/jdbc/SQLServerException.java:196:in makeFromDatabaseError': NativeException: com.microsoft.sqlserver.jdbc.SQLServerException: The object 'FK__POSTS__COMMENT_I__42E1EEFE' is dependent on column 'COMMENT_ID'. (Sequel: :DatabaseError) from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:1454:in getNextResult' from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:786:in doExecuteStatement' from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:685:in doExecute' from com/microsoft/sqlserver/jdbc/IOBuffer.java:4026:in execute' from com/microsoft/sqlserver/jdbc/SQLServerConnection.java:1416:in executeCommand' from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:185:in executeCommand' from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:160:in executeStatement' from com/microsoft/sqlserver/jdbc/SQLServerStatement.java:658:in execute' ... 9 levels... from migration.rb:27:indown' from C:/jruby/lib/ruby/gems/1.8/gems/sequel-3.8.0/lib/sequel/extensions/migration.rb:65:in `apply' from migration.rb:34

What version of the product are you using? On what operating system? Sequel 3.8.0. Windows Server 2003

Please provide any additional information below.

On SQL Server, [COMMENT_ID] integer REFERENCES [COMMENTS] generates a key with a random suffix in its name. That key needs to be deleted before the column can be deleted.

The key can be named using the following syntax: [COMMENT_ID] integer NOT NULL, CONSTRAINT [fk_POSTS_COMMENT_ID] FOREIGN KEY ([COMMENT_ID]) REFERENCES COMMENTS

Can Sequel establish a convention for naming FK's on SQL Server and use the named key syntax for creation/deletion of foreign key columns?

You can provide a :name option to foreign_key to specify the constraint name, and then drop the constraint yourself before dropping the table using drop_constraint. Sequel has no way to parse constraints from tables before dropping them, so this can't be done automatically. On most other databases, dropping the table automatically drops the related constraints, so this isn't an issue.

I am not able to get this to work. Seems like the :name option to foreign_key changes the name of the column, not the associated constraint.

posts = Class.new(Sequel::Migration) do def up create_table :posts do primary_key :id foreign_key :comment_id, :name => 'fk_posts_comment_id', :table => :comments end end

def down alter_table :posts do drop_constraint 'fk_posts_comment_id' end end end

I, [2010-02-03T09:21:03.725000 #6968] INFO -- : CREATE TABLE [COMMENTS] ([ID] integer PRIMARY KEY IDENTITY(1,1)) I, [2010-02-03T09:21:04.131000 #6968] INFO -- : CREATE TABLE [POSTS] ([ID] integer PRIMARY KEY IDENTITY(1,1), [FK_POSTS _COMMENT_ID] integer REFERENCES [COMMENTS]) I, [2010-02-03T09:21:04.147000 #6968] INFO -- : ALTER TABLE [POSTS] DROP CONSTRAINT [FK_POSTS_COMMENT_ID] com/microsoft/sqlserver/jdbc/SQLServerException.java:196:in `makeFromDatabaseError': NativeException: com.microsoft.sqls erver.jdbc.SQLServerException: 'FK_POSTS_COMMENT_ID' is not a constraint. (Sequel::DatabaseError)

On most other databases, dropping the table automatically drops the related constraints, so this isn't an issue

Yes. dropping the table works. Dropping a foreign key column does not. My code sample was intended to illustrate the latter. I should have separated the FK column creation and drop into another class to make that clear.

Sorry, I forgot to mention that you also have to use a table level foreign key constraint instead of including it in the column definition:

posts = Class.new(Sequel::Migration) do def up create_table :posts do primary_key :id Integer :comment_id foreign_key [:comment_id], :comments, :name => 'fk_posts_comment_id' end end

def down alter_table :posts do drop_constraint 'fk_posts_comment_id' end end end

Verified. Thanks Jeremy.