Rails Generate Migration Add Column Foreign Key

Posted on by

Rails migration for belongsto association with custom table name. The migration tries to add a foreign key for a not existing table. Runs without any failures and produces a correct result that’s the created payments table with the receiverid column and a foreign key points to the joined users table. So, the solution is to use. Before renaming a table, you should verify that any foreign key constraints on the table have an explicit name in your migration files instead of letting Laravel assign a convention based name. Otherwise, the foreign key constraint name will refer to the old table name. Creating Columns. Script/generate can take table name. As far as I can tell script/generate will happily take the plural table name, at least in Rails 2.3. Mar 21, 2018  Migration is a set of database instruction. They describe database changes. In migration we can create table, change table, add column, remove column, drop table and many more. We can migrate our.

Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

There are many ways to add the foreign key column that's required by our has_many and belongs_to associations, and I want to take a moment to show you a couple more of them.

There are many ways to add the foreign key column that's required by our has_many and belongs_to associations, and I want to take a moment to show you a couple more of them.

  • If you want, you can use the references column type in migrations instead.
    • bin/rails generate migration AddPostToComments post:references
    • That will create a migration with a call to the add_reference method instead of add_column. add_reference takes a symbol with a table name, and a symbol with the name of a model to add a foreign key for. It'll create a column whose name begins with that model name, and ends in _id. And since it's always desirable to have an index on foreign key columns, add_reference will add an index as well. So add_reference :comments, :post will create a post_id column in the comments table just like add_column did, but it will also add an index on that column automatically.
    • The foreign_key: true argument will set up a foreign key constraint on databases that support it.

Without a foreign key constraint, we could create a comment with a post_id field set to 999, even if there was no record in the post table with an id of 999. With a foreign key constraint, the database would prevent such a record from even being saved. Foreign key constraints help keep bad data from sneaking into your database.

Note that the adapter for the SQLite database that Rails uses by default doesn't support foreign key constraints. Your migration will still work, and it's a good idea to get in the habit of adding the constraints in your migrations. But if you want the database to actually enforce the constraints, you'll need to switch to another database like MySQL or PostgreSQL.

If we know we're going to need an association when we're first creating a model, we can set the necessary columns up then, too.

  • Let's re-generate the Comment model. We can replace the two migrations with a single migration that creates the comments table and adds a post_id column along with the other columns: rails g model Comment content:text name:string post:references.
    • We can allow it to overwrite the existing model class file, as well as another file related to tests.
    • Don't forget to run bin/rails db:migrate.
    • We should now be able to create comments associated with a post again.
  • 0:00

    There are many ways to add the foreign_keycolumn that's required by our has_many and

  • 0:04

    belongs_to associations.

  • 0:06

    And I wanna take a moment toshow you a couple more of them.

  • 0:09

    Let's undo our changes to the databaseby rolling back the latest migration.

  • 0:13

    We'll run bin/rails.

  • 0:16

    And we'll run the db:rollback task.

  • 0:20

    That'll go through the commands in thelatest migration and undo them one by one.

  • 0:25

    Then let's get rid ofthe migration we created.

  • 0:27

    We're gonna do thatwith bin/rails destroy,

  • 0:31

    which you can consider to bethe opposite of the generate command.

  • 0:35

    We're gonna destroy the migrationnamed AddPostToComments.

  • 0:44

    That's the latest migration we created.

  • 0:47

    That'll go through,

  • 0:48

    find all the files that were createdby the generator, and delete them.

  • 0:52

    Now let's redo our previous migrationusing the references column type instead.

  • 0:56

    So we're gonna run bin/rails generate.

  • 1:02

    We're gonna generate a migration.

  • 1:04

    And again,it'll be named AddPostToComments.

  • 1:09

    This time, instead of a type of integer,we're going to give it

  • 1:14

    a column name of post anda type of references.

  • 1:21

    That'll create a new migration file.

  • 1:27

    And the file will have a call tothe add_reference method instead of

  • 1:31

    add_column.

  • 1:32

    add_reference takes a symbol with a tablename and a symbol with the name of

  • 1:36

    a model to add a foreign_key for,in this case, the post model.

  • 1:41

    It'll create a column whose namebegins with that model name and

  • 1:45

    ends in underscore ID.

  • 1:47

    And since it's always desirable tohave an index on foreign_key columns,

  • 1:50

    add_reference will add an index as well.

  • 1:53

    So add_reference :comments,

  • 1:54

    :post will create a post_id column in thecomments table, just like add_column did.

  • 1:59

    But it'll also add an indexon that column automatically.

  • 2:03

    The foreign_key: true argument willset up a foreign_key constraint

  • 2:07

    on databases that support it.

  • 2:10

    Without a foreign_key constraint, we couldcreate a comment with a post_id field set

  • 2:14

    to 999, even if there was no recordin the post table with an ID of 999.

  • 2:21

    But with a foreign_key constraint,

  • 2:23

    the database would prevent sucha record from even being saved.

  • 2:27

    Note that the adapter for the SQL-likedatabase that Rails uses by default

  • 2:31

    doesn't support foreign_key constraints.

  • 2:34

    Your migration will still work.

  • 2:35

    And it's a good idea to get in the habitof adding the constraints in your

  • 2:38

    migrations.

  • 2:40

    But if you want the database toactually enforce the constraints,

  • 2:43

    you'll need to switch to anotherdatabase like MySQL or PostgreSQL.

  • 2:47

    See the teacher's notes if you'dlike more info on doing so.

  • 2:51

    Now that we have our new migration,let's try running it.

  • 2:54

    bin/rails db:migrate.

  • 3:01

    It'll add a post_id field backto our comments table again.

  • 3:04

    And if we launch a Rails console, we'll beable to add comments to our post again.

  • 3:11

    So if we were to take the first post and

  • 3:16

    create a comment on itwith comments.create,

  • 3:21

    we'll give it content of HI.

  • 3:27

    And the commenter name is gonna be Jay.

  • 3:31

    Generate private public key pair mac. And if we pretty print the listof the first post comments,

  • 3:37

    we can see the comment's been added.

  • 3:42

    If we know we're going to needan association when we're first

  • 3:45

    creating a model, we can setthe necessary columns up then, too.

  • 3:49

    Let's start by getting our databaseback to the point it was at

  • 3:52

    before we created the comments model.

  • 3:54

    Since we've only set up the commentstable on our development machine,

  • 3:57

    we can just undo the migrationsthat created it.

  • 4:00

    We can list out the migrations in ourdb/migrate directory with ls db/migrate.

  • 4:08

    You might need to use a slightly differentcommand to list the directory contents if

  • 4:12

    you're on Windows.

  • 4:13

    We can see that the two most recentmigrations are add_post_to_comments and

  • 4:18

    create_comments.

  • 4:20

    Let's roll back our migration that addsthe post_id field to the comments table.

  • 4:24

    bin/rails db:rollback.

  • 4:30

    Then let's roll back the migrationthat created the comments table.

  • 4:33

    bin/rails db:rollback again.

  • 4:36

    Now let's get rid of thoselast two migrations.

  • 4:39

    bin/rails destroy,

  • 4:44

    The migration named AddPostToComments.

  • 4:52

    And then we'll also destroythe migration named CreateComments.

  • 5:00

    Now let's regenerate the comment model.

  • 5:02

    We can replace the two migrations with asingle migration that creates the comments

  • 5:07

    table and adds a post_id columnalong with the other columns.

  • 5:10

    So we'll say bin/rails

  • 5:14

    generate model Comment.

  • 5:19

    We'll set up a contentattribute of type text and

  • 5:24

    commenter name attribute of type string.

  • 5:28

    And post:references willset up the post_id field.

  • 5:35

    If it asks to overwrite any existingfiles, it should be okay to allow it.

  • 5:39

    We generated the migration, sodon't forget to run bin/rails db:migrate.

  • 5:47

    And if we launch bin/rails console,

  • 5:52

    we should be able to create commentsassociated with post again.

  • 5:55

    So let's say post = Post.first,

  • 6:01

    post.comments.create.

  • 6:05

    And we'll give it content of Hi anda commenter name of Alena.

  • 6:15

    And if we run Post.first.comments,it should bring up our new comment.

  • 6:22

    Now you know several ways tosetup a has_many association.

  • 6:26

    In the next few videos, we're goingto take a look at some of the other

  • 6:28

    associations that Active Record supports

You need to sign up for Treehouse in order to download course files.

Migrations can manage the evolution of a schema used by several physical databases. It's a solution to the common problem of adding a field to make a new feature work in your local database, but being unsure of how to push that change to other developers and to the production server. With migrations, you can describe the transformations in self-contained classes that can be checked into version control systems and executed against another database that might be one, two, or five versions behind.

Example of a simple migration:

This migration will add a boolean flag to the accounts table and remove it if you're backing out of the migration. It shows how all migrations have two methods up and down that describes the transformations required to implement or remove the migration. These methods can consist of both the migration specific methods like add_column and remove_column, but may also contain regular Ruby code for generating data needed for the transformations.

Example of a more complex migration that also needs to initialize data:

This migration first adds the system_settings table, then creates the very first row in it using the Active Record model that relies on the table. It also uses the more advanced create_table syntax where you can specify a complete table schema in one block call.

Available transformations

Creation

  • create_join_table(table_1, table_2, options): Creates a join table having its name as the lexical order of the first two arguments. See ActiveRecord::ConnectionAdapters::SchemaStatements#create_join_table for details.

  • create_table(name, options): Creates a table called name and makes the table object available to a block that can then add columns to it, following the same format as add_column. See example above. The options hash is for fragments like “DEFAULT CHARSET=UTF-8” that are appended to the create table definition.

  • add_column(table_name, column_name, type, options): Adds a new column to the table called table_name named column_name specified to be one of the following types: :string, :text, :integer, :float, :decimal, :datetime, :timestamp, :time, :date, :binary, :boolean. A default value can be specified by passing an options hash like { default: 11 }. Other options include :limit and :null (e.g. { limit: 50, null: false }) – see ActiveRecord::ConnectionAdapters::TableDefinition#column for details.

  • add_foreign_key(from_table, to_table, options): Adds a new foreign key. from_table is the table with the key column, to_table contains the referenced primary key.

  • add_index(table_name, column_names, options): Adds a new index with the name of the column. Other options include :name, :unique (e.g. { name: 'users_name_index', unique: true }) and :order (e.g. { order: { name: :desc } }).

  • add_reference(:table_name, :reference_name): Adds a new column reference_name_id by default an integer. See ActiveRecord::ConnectionAdapters::SchemaStatements#add_reference for details.

  • add_timestamps(table_name, options): Adds timestamps (created_at and updated_at) columns to table_name.

Modification

  • change_column(table_name, column_name, type, options): Changes the column to a different type using the same parameters as add_column.

  • change_column_default(table_name, column_name, default_or_changes): Sets a default value for column_name defined by default_or_changes on table_name. Passing a hash containing :from and :to as default_or_changes will make this change reversible in the migration.

  • change_column_null(table_name, column_name, null, default = nil): Sets or removes a +NOT NULL+ constraint on column_name. The null flag indicates whether the value can be NULL. See ActiveRecord::ConnectionAdapters::SchemaStatements#change_column_null for details.

  • change_table(name, options): Allows to make column alterations to the table called name. It makes the table object available to a block that can then add/remove columns, indexes or foreign keys to it.

  • rename_column(table_name, column_name, new_column_name): Renames a column but keeps the type and content.

  • rename_index(table_name, old_name, new_name): Renames an index.

  • rename_table(old_name, new_name): Renames the table called old_name to new_name.

Deletion

  • drop_table(name): Drops the table called name.

  • drop_join_table(table_1, table_2, options): Drops the join table specified by the given arguments.

  • remove_column(table_name, column_name, type, options): Removes the column named column_name from the table called table_name.

  • remove_columns(table_name, *column_names): Removes the given columns from the table definition.

  • remove_foreign_key(from_table, to_table = nil, **options): Removes the given foreign key from the table called table_name.

  • remove_index(table_name, column: column_names): Removes the index specified by column_names.

  • remove_index(table_name, name: index_name): Removes the index specified by index_name.

  • remove_reference(table_name, ref_name, options): Removes the reference(s) on table_name specified by ref_name.

  • remove_timestamps(table_name, options): Removes the timestamp columns (created_at and updated_at) from the table definition.

Irreversible transformations

Some transformations are destructive in a manner that cannot be reversed. Migrations of that kind should raise an ActiveRecord::IrreversibleMigration exception in their down method.

Running migrations from within Rails

The Rails package has several tools to help create and apply migrations.

To generate a new migration, you can use

where MyNewMigration is the name of your migration. The generator will create an empty migration file timestamp_my_new_migration.rb in the db/migrate/ directory where timestamp is the UTC formatted date and time that the migration was generated.

There is a special syntactic shortcut to generate migrations that add fields to a table.

This will generate the file timestamp_add_fieldname_to_tablename.rb, which will look like this:

To run migrations against the currently configured database, use rails db:migrate. This will update the database by running all of the pending migrations, creating the schema_migrations table (see “About the schema_migrations table” section below) if missing. It will also invoke the db:schema:dump command, which will update your db/schema.rb file to match the structure of your database.

To roll the database back to a previous migration version, use rails db:rollback VERSION=X where X is the version to which you wish to downgrade. Alternatively, you can also use the STEP option if you wish to rollback last few migrations. rails db:rollback STEP=2 will rollback the latest two migrations.

If any of the migrations throw an ActiveRecord::IrreversibleMigration exception, that step will fail and you'll have some manual work to do.

Database support

Migrations are currently supported in MySQL, PostgreSQL, SQLite, SQL Server, and Oracle (all supported databases except DB2).

More examples

Key

Not all migrations change the schema. Some just fix the data:

Others remove columns when they migrate up instead of down:

And sometimes you need to do something in SQL not abstracted directly by migrations:

Using a model after changing its table

Sometimes you'll want to add a column in a migration and populate it immediately after. In that case, you'll need to make a call to Base#reset_column_information in order to ensure that the model has the latest column data from after the new column was added. Example:

Controlling verbosity

By default, migrations will describe the actions they are taking, writing them to the console as they happen, along with benchmarks describing how long each step took.

You can quiet them down by setting ActiveRecord::Migration.verbose = false.

You can also insert your own messages and benchmarks by using the say_with_time method:

The phrase “Updating salaries…” would then be printed, along with the benchmark for the block when the block completes.

Timestamped Migrations

By default, Rails generates migrations that look like:

The prefix is a generation timestamp (in UTC).

If you'd prefer to use numeric prefixes, you can turn timestamped migrations off by setting:

In application.rb.

Reversible Migrations

Reversible migrations are migrations that know how to go down for you. You simply supply the up logic, and the Migration system figures out how to execute the down commands for you.

To define a reversible migration, define the change method in your migration like this:

This migration will create the horses table for you on the way up, and automatically figure out how to drop the table on the way down.

Some commands cannot be reversed. If you care to define how to move up and down in these cases, you should define the up and down methods as before.

If a command cannot be reversed, an ActiveRecord::IrreversibleMigration exception will be raised when the migration is moving down.

For a list of commands that are reversible, please see ActiveRecord::Migration::CommandRecorder.

Transactional Migrations

If the database adapter supports DDL transactions, all migrations will automatically be wrapped in a transaction. There are queries that you can't execute inside a transaction though, and for these situations you can turn the automatic transactions off.

Remember that you can still open your own transactions, even if you are in a Migration with self.disable_ddl_transaction!.

  • MODULEActiveRecord::Migration::Compatibility
  • CLASSActiveRecord::Migration::CheckPending
  • CLASSActiveRecord::Migration::CommandRecorder
Methods
#
A
C
  • check_pending!,
  • connection,
  • copy,
D
  • disable_ddl_transaction!,
E
L
M
  • method_missing,
  • migrate,
N
  • new,
P
R
  • reversible,
  • revert,
  • reverting?,
S
  • say,
  • say_with_time,
U
  • up,
W
[RW] name
[RW] version
Class Public methods

Source: show on GitHub

Raises ActiveRecord::PendingMigrationError error if any migrations are pending.

Source: show on GitHub

Source: show on GitHub

Disable the transaction wrapping this migration. You can still create your own transactions even after calling disable_ddl_transaction!

For more details read the “Transactional Migrations” section above.

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Instance Public methods

Rails Generate Migration Foreign Key

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Source: show on GitHub

Execute this migration in the named direction

Source: show on GitHub

Determines the version number of the next migration.

Source: show on GitHub

Finds the correct table name given an Active Record object. Uses the Active Record object's own table_name, or pre/suffix from the options passed in.

Source: show on GitHub

Used to specify an operation that can be run in one direction or another. Call the methods up and down of the yielded object to run a block only in one given direction. The whole block will be called in the right order within the migration.

In the following example, the looping on users will always be done when the three columns 'first_name', 'last_name' and 'full_name' exist, even when migrating down:

Source: show on GitHub

Reverses the migration commands for the given block and the given migrations.

The following migration will remove the table 'horses' and create the table 'apples' on the way up, and the reverse on the way down.

Or equivalently, if TenderloveMigration is defined as in the documentation for Migration:

This command can be nested.

Source: show on GitHub

Source: show on GitHub

Runs the given migration classes. Last argument can specify options:

  • :direction (default is :up)

  • :revert (default is false)

Source: show on GitHub

Takes a message argument and outputs it as is. A second boolean argument can be passed to specify whether to indent or not.

Add Numbers

Source: show on GitHub Free pubg steam key generator.

Outputs text along with how long it took to run its block. If the block returns an integer it assumes it is the number of rows affected.

Source: show on GitHub

Takes a block as an argument and suppresses any output generated by the block.

Source: show on GitHub

Source: show on GitHub

Used to specify an operation that is only run when migrating up (for example, populating a new column with its initial values).

In the following example, the new column published will be given the value true for all existing records.

Source: show on GitHub

Source: show on GitHub