Update Migration Guide for add_foreign_key

[jvanderen1]

When using add_foreign_key in a migration within a change block and an array with 1 symbol, this does appear to go down properly. Therefore, updating the migration guide might be helpful for others.

Example:

Sequel.migration do
  change do
    alter_table :projects do
      add_foreign_key([:user_id], :users, not_valid: true)
    end
  end
end

• Migrating this up: Within the the projects, this creates a user_id column + foreign key constraint pointing to the users table (invalid)
• Migrating this down: Within the the projects, this drops the foreign key constraint within the user_id column pointing to the users table

[jeremyevans]

When you pass an array of columns, Sequel should only attempt to create the foreign key constraint, not add a column:

DB.alter_table :projects do
  add_foreign_key([:user_id], :users, not_valid: true)
end
# ALTER TABLE "projects" ADD FOREIGN KEY ("user_id") REFERENCES "users" NOT VALID

This isn't reversible as you did not name the foreign key constraint. Can you show an example of Sequel creating a column when passing an array of columns?

[jvanderen1]

Absolutely! Here is a minimal example which:

• Has a user_id column which already exists
• Has no foreign key constraint

Example

Given a projects table schema which looks like:

                                               Table "public.projects"
           Column            |            Type             | Collation | Nullable |             Default
-----------------------------+-----------------------------+-----------+----------+----------------------------------
 id                          | integer                     |           | not null | generated by default as identity
 user_id                     | integer                     |           | not null |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)

I run the example migration in the description up. This generates a foreign key constraint:

                                               Table "public.projects"
           Column            |            Type             | Collation | Nullable |             Default
-----------------------------+-----------------------------+-----------+----------+----------------------------------
 id                          | integer                     |           | not null | generated by default as identity
 user_id                     | integer                     |           | not null |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)
Foreign key constraints:
    "projects_user_id_fkey"  | (user_id) REFERENCES users(id) NOT VALID

I run the example migration in the description down. This removes the foreign key constraint:

                                               Table "public.projects"
           Column            |            Type             | Collation | Nullable |             Default
-----------------------------+-----------------------------+-----------+----------+----------------------------------
 id                          | integer                     |           | not null | generated by default as identity
 user_id                     | integer                     |           | not null |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (id)

Note

To your point:

Sequel should only attempt to create the foreign key constraint, not add a column

If the column user_id did not already exist, the migration would actually fail at up! So there are specific cases where an array won't work.

[jeremyevans]

From my testing, it looks like it works in the cases where:

• The database supports foreign_key_list
• There is only a single foreign key constraint for the columns

In this case, Sequel can look up the foreign key name and drop the constraint. This appears to be supported on common databases, but not on all databases that Sequel ships support for. I would guess multiple foreign key constraints on the same columns are rare. So for most Sequel usage, it probably does work.

I'll update the documentation to reflect that. Thank you very much for your perseverance.

[jvanderen1]

Thanks @jeremyevans!