laravelbook.com

Warning: Work in Progress!

This site contains a a few lessons on Laravel 4. It's still in it's early phases. Besides, Laravel 4 itself is a moving target and a few things may have changed by the time you read this.

I will regularly update the content. Thanks!

Managing Databases with Migrations

Laravel encourages an agile, iterative style of development. We don’t expect to get everything right the first time. Instead we write code, tests and interact with our end-users to refine our understanding as we go.

For that to work, we need a supporting set of practices. We use version control tools like subversion, git or mercurial to store our application’s source code files, allowing us to undo mistakes and to track what changes during the course of development.

But there’s another area of the application that changes, an area that cannot be effectively managed using version control alone. The database schema in a Laravel application constantly evolves as we progress through the development: we add a table here, rename a column there, drop an index and so on. The database changes in step with the application’s code.

Typically there are several situation where you need a sophisticated way to track your database schema changes:

  • When you work within a team of developers, each person needs to know about any schema change.

  • When you deploy on a production server, you need to have a robust way to upgrade your database schema.

  • If you work on several machines, you need to keep all database schemas synchronized.

Without strict conventions and discipline for the application developers to follow, keeping the database schema in sync with application code is traditionally a very troublesome job. Developers (or database administrators) make schema changes as needed. However, if the application code is rolled back to a previous version, it was hard to undo the database schema changes to bring the database back in line with that prior application version ΓÇö the database itself has no versioning information.

Migrations are the Laravel way of helping you to evolve the database schema of your application (also known as its DDL) without having to drop and re-create the database each time you make a change. And not having to drop and recreate the database each time a change happens means that you don’t lose your development data. The only changes made when you execute a migration are those necessary to move the schema from one version to another, whether that move is forward or backward in time.

Not only does Laravel migration provide you with a means to change your database schema in an iterative manner, but it lets you do so using PHP code, rather than SQL! The Laravel Schema Builder allows us to create database tables and insert columns or indices quickly. It uses clean and expressive syntax to make database operations happen. You may think of Laravel migration as version control for your databases!

By defining a higher level interface to creating and maintaining the database schema, you can define it in a database agnostic way. By using PHP methods to create tables and define columns or indices, you can write the schema once and apply it to any database backend supported. The added bonus is that Laravel keeps track of which migrations have been applied already and which ones still need to be applied.

Migration Basics

A Laravel migration is simply a PHP source file in your application’s app/database/migrations folder. Each file contains a discrete set of changes to the underlying database. Changes to the database are made in PHP rather than a database-specific flavour of SQL. Your PHP migration code ends up being converted into the DDL specific to your current database; this makes switching database platforms very easy. Since migrations are kept in their own directory, it’s pragmatic to include them into version control just like any other project code. Laravel migrations are run explicitly from command-line using the artisan tool.

Migration File Naming Conventions

In older versions of Laravel, migration files had relatively simple names, such as 001_create_authors_table.php. Laravel 3 brought a new naming convention, in which the first part of the name changed from a sequential number to a much longer timestamp, like 2012_12_25_161424_create_authors_table.php. The name of the file is of the form YYYY_MM_DD_HHMMSS_some_meaningful_name.php, that is to say a UTC timestamp identifying the migration followed by an underscore followed by the name of the migration.

The new wider names help avoid name collisions if you’re a developer working on a team where multiple people can check in their own migrations.

Additionally, Laravel migration files are time-stamped so that they can be executed sequentially. The timestamp digits are the key to migrations, because they define the sequence in which the migrations are applied ΓÇö they are the individual migration’s version number.

Like SQL scripts, migrations can be built on top of each other, which reinforces the need for these files to be executed in order. Sequential execution removes the possibility of, for example, any attempt to add a new column to a table that doesn’t yet exist.

Although you could create these migration files by hand, it’s easier (and less error prone) to use the Artisan command-line tool to generate the migration scripts. You can edit those files afterward as needed.

Running Migrations Forward and Backward

You apply migrations to the database using the artisan tool. Laravel provides a set of artisan tasks to work with migrations which boil down to running certain sets of migrations.

Note: You can run artisan list to see the list of tasks it supports, and most of the database migration-related tasks are prefixed with migrate:.

There are only a few tasks that you really need to know:

migrate:install

The very first migration related artisan task you will use will probably be artisan migrate:install. Internally, Laravel uses a special table to keep track of which migrations have already run. To create this table, just use the artisan command-line tool:

$ php artisan migrate:install

migrate

You’ll run the migrate task frequently to update your database to support the latest tables and columns you’ve added to your application. In its most basic form it just runs the up() method for all the migrations that have not yet been run. If there are no such migrations, it exits. It will run these migrations in order based on the date of the migration.

migrate:rollback

Occasionally you will make a mistake when writing a migration. If you have already run the migration then you cannot just edit the migration and run the migration again: Laravel assumes it has already run the migration and so will do nothing when you run artisan migrate. You must rollback the migration using artisan migrate:rollback, edit your migration and then run artisan migrate to run the corrected version.

In general editing existing migrations is not a good idea: you will be creating extra work for yourself and your co-workers and cause major headaches if the existing version of the migration has already been run on production machines. Instead, you should write a new migration that performs the changes you require.

Note: artisan migrate:rollback will let you remove the last migration applied. Laravel rolls back the entire migration “operation”. So, if the last migration command ran 15 migrations, all 15 migrations would be rolled back. Be careful: when Laravel deletes a column or table, it discards the data.

migrate:reset

This task will roll back all migrations that have ever run (it obliterates all the tables and data)

migrate:refresh

The artisan migrate:refresh task will drop the database, recreate it and load the current schema into it. This is a convenience shortcut to run reset and subsequently re-run all migrations.

migrate:make

The artisan migrate:make command tells Laravel to generate a skeleton migration file (which is actually a PHP file) in the app/database/migrations folder. You can then edit this file to flesh out your table/index definition. Later, when you run the artisan migrate command, Artisan will consult this file to generate actual SQL DDL code.

Creating Migrations

Let’s get started by creating a MySql database “laravel_db”. Next open up the database.php file in the app/config directory of your Laravel application. Make sure that the default key is set to mysql:

return array(
        ...
        'default' => 'mysql',

Then enter your database information:

...
'connections' => array(
        'mysql' => array(
                'driver'    => 'mysql',
                'host'      => '127.0.0.1',
                'database'  => 'laravel_db',
                'username'  => 'Your_Database_Username',
                'password'  => 'Your_Database_Password',
                'charset'   => 'utf8',
                'collation' => 'utf8_unicode_ci',
                'prefix'    => '',
        ),
...

We are going to use Laravel’s command line tool artisan to create our new migration. To run artisan you will need to open a terminal to the root of your Laravel application folder where the artisan script exists.

The first thing we need to do is to install to install the migrations table, so that Laravel can keep track of which migrations have been run. The following command will create the special table in our database:

$ php artisan migrate:install

If successful, artisan will respond with “Nice! Now we’re ready to do some migrating!”.

If you check the database, you’ll find that artisan has indeed created a new table “migrations”:

MariaDB [laravel_db]> SHOW TABLES;
+----------------------+
| Tables_in_laravel_db |
+----------------------+
| migrations . . . . . |
+----------------------+
1 row in set (0.00 sec)

You don’t need to be overly concerned about the table. This is simply a table that helps Laravel to keep tabs on the migrations that you have and have not run. When you add new migrations, artisan migrate will check the migrations table and execute all migrations that have not yet run.

Now, let’s create an actual migration file. We’d like to create a new table named “authors”. Let’s run the artisan migrate:make command:

$ php artisan migrate:make create_authors_table
Migration created successfully!

We are telling artisan to run the make method on the migrate task and we pass an underscored delimited list of words which describe our migration in human readable terms. You may name the migration as you wish, but you may want to give it a name that describes exactly what you’re doing. Laravel does not care what the migration is called, it is just for your sanity. In this case we are creating the “authors” table, therefore we’ve named our migration create_authors_table.

Note: The detail-oriented reader may have noticed that we called our author table “authors” instead of “author”. This is an aspect of Laravel’s design philosophy, which uses natural language to help us store the data model. Laravel tables should always be named the pluralized version of your model’s name. In the case of the Author model, the table was named “authors”. If you had a model called Car, you would create a table called “cars”. Besides, it just makes more sense to say SELECT name FROM authors WHERE id=100 than SELECT name FROM author WHERE id=100.

$ php artisan migrate:make create_authors_table --create --table authors
Migration created successfully!

If you run that command you’ll see that a new migration has been added. Here’s what the app/database/migrations directory of looks like:

$ ls app/database/migrations
2012_12_25_161424_create_authors_table.php

As you can see, we now have a new migration file whose name contains time-stamp as well as the descriptive name of our migration.

Great! Let’s open up the newly generated file and take a look at the migration class.

Anatomy of a Migration

Migrations are subclasses of the Laravel class Illuminate\Database\Migrations\Migration. The class you create must contain at least the two class methods up() and down(). Below is the skeleton migration class generated by artisan:

<?php

use Illuminate\Database\Migrations\Migration;

class CreateAuthorsTable extends Migration {

        /**
        * Run the migrations.
        */
        public function up()
        {
                //
        }

        /**
        * Reverse the migrations.
        */
        public function down()
        {
                //
        }

}

With Laravel you can migrate to specific versions of the data model at any point in time. The code in the up() method is executed when migrating forward while down() is executed when migrating backward (that is, creating a new version of the database or rolling back to a previous version).

An easy way to think about it is that up() is the action you want to perform in the migration file, and down() is the exact opposite. It is just like using the Undo command in a word processor application - you’re just undoing the changes you made. So, for example, if you want to create a table called ‘authors’, you create it in the up() method and then destroy it in down(). Let’s look at how you do that.

The Artisan migrate:make command has some additional options that can speed up your workflow. Let’s run the following command:

$ php artisan migrate:make create_authors_table --table authors --create
Migration created successfully!

In the example above, we specify the name of the table using the --table option. Additionally, we include the --create option to inform artisan that the table needs to be created. If you open up the migration file you’ll see that artisan has generated additional boilerplate code for us:

<?php

use Illuminate\Database\Migrations\Migration;

class CreateAuthorsTable extends Migration {

        public function up()
        {
                Schema::create('authors', function($table)
                {
                        $table->increments('id');
                });
        }

        public function down()
        {
                Schema::drop('authors');
        }

}

Great! Now, let’s make this more concrete. We’re going to use the Laravel Schema class to create our “authors” table. Here’s the migration code that creates the authors table and adds necessary columns to the table:

public function up()
{
          Schema::create('authors', function($t) {
              // auto increment id (primary key)
              $t->increments('id');

              $t->string('name');
              $t->integer('age')->nullable();
              $t->boolean('active')->default(1);
              $t->integer('role_id')->unsigned();
              $t->text('bio');

              // created_at, updated_at DATETIME
              $t->timestamps();
          });
}

We call the Schema::create() method to create a new table named ‘authors’. Schema::create() takes two parameters: the name of the table (remember, table names are plural) and a closure containing column definitions. The closure is passed a table definition object, which we use to define the columns in the table. You can call the parameter whatever you like (i.e. $table), but I like using $t (because it involves less typing).

Inside the closure, we can use the $t parameter to create our columns with a number of handy methods. Schema methods are named according to the data types they represent. The basic structure of a column definition method is $t->column_type(column_name)

Column Types

In the previous example, we specified that the ‘name’ column has a type of “string”. But just what does this mean? Databases typically don’t have column types of “string”.

Remember that Laravel tries to make your application independent of the underlying database; you could develop using MySql and deploy to Postgresql if you wanted, for example. But different databases use different names for the types of columns. If you used a MySql column type in a migration, that migration might not work if applied to a Postgres database. So, Laravel migrations insulate you from the underlying database type systems by using generic data types. If we’re migrating a MySql database, the string() method will create a column of type VARCHAR(255). On Postgres, the same migration might add a column with the type CHAR VARYING(255) (although the VARCHAR type is supported by all major database platforms).

Laravel supports the following data types directly:

Laravel Method Column Type
increments($column) Adds an auto-incrementing primary key to the table
string($column) Adds a VARCHAR(255) column
string($column, $length) Adds a VARCHAR equivalent with a length
integer($column) Adds an INTEGER column to the table
float($column) Adds a FLOAT column to the table
decimal($column, $precision, $scale) Adds a DECIMAL column with a precision and scale. Precision is the total number of digits in a number. Scale is the number of digits to the right of the decimal point. For example, the number 123.45 has a precision of 5 and a scale of 2. Logically, the scale cannot be larger than the precision.
boolean($column) Adds a BOOLEAN column to the table. Note: The way that boolean values are stored varies from database to database. Some use “1” and “0” integer values to represent true and false, respectively, while others use characters such as “T” and “F”. Laravel’s “boolean” type maps to a small integer column on all database systems. Laravel handles the mapping between PHP’s true and false very well, so you don’t need to worry about the underlying scheme yourself.
text($column) TEXT equivalent to the table
blob($column) BLOB equivalent to the table
binary($column) BINARY equivalent to the table
dateTime($column) DATETIME equivalent to the table
timestamp($column) TIMESTAMP equivalent to the table
date($column) DATE equivalent to the table
time($column) TIME equivalent to the table
enum($column, array $allowed) Create a new ENUM column on the table

Additionally, there are a few more extension methods that you need to be aware of:

Laravel Method Column Type
timestamps() timestamps() is not the same as timestamp(). It is a convenience method Laravel uses to manage creation and modification times as created_at and updated_at TIMESTAMP columns. They are two special database columns that Laravel can modify on its own. The created_at column is modified only when the row is created with the current timestamp. On the other hand, updated_at is modified with the current timestamp each time the row’s data is manipulated.
nullable() Designate that the column allows NULL values. By default, Laravel makes the column required at the database level by adding a NOT NULL constraint.
default($value) Sets a default to be used as the initial value of the column for new rows. You don’t ever need to explicitly set the default value to null. Just leave off this option to get a null default value.
unsigned() Set INTEGER column to UNSIGNED

Default Assumptions

All column definitions start out with some default assumptions, which you can override as needed.

  • Laravel assumes every table has a numeric primary key (usually named “id”) and ensures the value of this column is unique for each new row added to the table. Laravel doesn’t really work well unless each table has a numeric primary key. So, for your average Laravel application, please ensure that you define a primary key using the increments() method.

  • Columns are NOT NULL by default

Now let’s analyze our schema builder code for the authors table line by line. Here’s the code from our up() method:

// auto increment id (primary key)
$t->increments('id');

$t->string('name');
$t->integer('age')->nullable();
$t->boolean('active')->default(1);
$t->integer('role_id')->unsigned();
$t->text('bio');

// created_at, updated_at DATETIME
$t->timestamps();

We start out by defining the primary key for our table:

$t->increments('id');

This generates the SQL:

`id` INT(11) NOT NULL AUTO_INCREMENT,

Create a column called name with a default length of 255 (NOT NULL constraint is implicitly enforced by Laravel):

$t->string('name');

This generates the SQL:

`name` VARCHAR(255) NOT NULL,

Create a numeric column called age (explicitly allow NULL values):

$t->integer('age')->nullable();

This generates the SQL:

`age` INT(11) NULL DEFAULT NULL,

Create a boolean column called active with a default value of 1:

$t->boolean('active')->default(1);

This generates the SQL:

`active` TINYINT(4) NOT NULL DEFAULT '1',

Create a numeric column called role_id and set it’s column type to UNSIGNED:

$t->integer('role_id')->unsigned();

This generates the SQL:

`role_id` INT(10) UNSIGNED NOT NULL,

Create a text field named bio:

$t->text('bio');

This generates the SQL:

`bio` TEXT NOT NULL,

Create two timestamp fields:

$t->timestamps();

This generates the SQL:

`created_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',

I hope you realize how powerful migrations can be. Instead of writing arcane SQL DDL statements, you just invoke some easy-to-remember Laravel Schema Builder methods!

Great, so now we have created our table! But what about when the user wants to rollback the migration? Since we created the table in the up() method, we now need to DROP the table in the down() method, so that the schema would return to its original form after a rollback. Fortunately the Schema class has a drop() method that will perform exactly that.

public function down()
{
        Schema::drop('authors');
}

The down() method is incredibly simple because it has only one line. All it does is remove the “authors” table from the database. If you’re familiar with basic SQL, it is the same as DROP TABLE authors. When we rollback the migration, Laravel will drop the “authors” table.

Okay, at this point we have written the schema. Now that you have created the migration file, you can execute it against the database. Let’s switch back to the command prompt (and go to the Laravel application root folder) and run the artisan migrate command:

$ php artisan migrate
Migrated: 2012_12_25_161424_create_authors_table

Great! If we take a look at our database the authors table has been created successfully:

MariaDB [laravel_db]> SHOW TABLES;
+----------------------+
| Tables_in_laravel_db |
+----------------------+
| authors    .   .   . |
| migrations .   .   . |
+----------------------+
2 rows in set (0.00 sec)

MariaDB [laravel_db]> DESCRIBE authors;
+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(11)          | NO   | PRI | NULL                | auto_increment |
| name       | varchar(255)     | NO   |     | NULL                |                |
| age        | int(11)          | YES  |     | NULL                |                |
| active     | tinyint(4)       | NO   |     | 1                   |                |
| role_id    | int(10) unsigned | NO   |     | NULL                |                |
| bio        | text             | NO   |     | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+------------+------------------+------+-----+---------------------+----------------+
8 rows in set (0.00 sec)

The database now has a table in which to store the author information, and you didn’t have much work to do to accomplish this. Just for comparison, this is what the SQL query looks like if you want to create your table by hand-writing the SQL statement:

CREATE TABLE `authors` (
        id          int AUTO_INCREMENT NOT NULL,
        name        varchar(255) NOT NULL,
        age         int,
        active      tinyint NOT NULL DEFAULT '1',
        role_id     int(10) UNSIGNED NOT NULL,
        bio         text NOT NULL,
        created_at  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        updated_at  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        email       varchar(64) NOT NULL,
        /* Keys */
        PRIMARY KEY (id)
) ENGINE = InnoDB;

I don’t know about you, but I’d rather write a few lines of PHP than try to match the syntax and data types of that SQL statement.

The next time you want to modify the data model, you can create a new migration and then run artisan migrate again. Each time you run the migrate command, it starts at the first migration file (based on the timestamp at the beginning of the filename) and checks to see whether it has been executed. If it has been run, it skips to the next file until it finds a starting point to begin executing. After it finds that point, it runs that migration and all migrations after that until it reaches the end.

Now, imagine we made a mistake and want to go back and undo the change. To do this, we simply use the artisan command like this:

$ php artisan migrate:rollback
Rolled back: 2012_12_25_161832_create_authors_table

It’s as easy as that. It will rollback the last migration you ran. Since we created the “authors” table in the previous migration, this command will drop the table. You can confirm this in the database:

MariaDB [laravel_db]> SHOW TABLES;
+----------------------+
| Tables_in_laravel_db |
+----------------------+
| migrations . . . . . |
+----------------------+
1 row in set (0.00 sec)

Let’s re-apply the migration:

$ php artisan migrate

After a while, you realize the author tables needs an “email” column and you need to alter the table schema. To do that, you simply create a new migration using the artisan migrate:make command. Since we are adding a new column to an existing table, we use a descriptive name to identify the new migration:

$ php artisan migrate:make add_email_to_authors_table
Migration created successfully!

Indeed, a new migration file has been added:

$ ls app\database\migrations
2012_12_25_161832_create_authors_table.php
2012_12_27_092602_add_email_to_authors_table.php

Let’s open up the newly generated migration script and add the “email” column to our table.

public function up()
{
        Schema::table('authors', function($t) {
                $t->string('email', 64);
        });
}

We use the Schema::table() method to begin working with the “authors” table. Inside the closure function, we add the “email” field.

Now, for version control, how do we undo that? Again we use the Schema::table() method to work on the table:

public function down()
{
        Schema::table('authors', function($t) {
                $t->dropColumn('email');
        });
}

We drop the newly added column using the dropColumn() method.

Let’s see if that works…

$ php artisan migrate
Migrated: 2012_12_27_092602_add_email_to_authors_table

You can verify the migration in the database:

MariaDB [laravel_db]> DESCRIBE authors;
+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(11)          | NO   | PRI | NULL                | auto_increment |
| name       | varchar(255)     | NO   |     | NULL                |                |
| age        | int(11)          | YES  |     | NULL                |                |
| active     | tinyint(4)       | NO   |     | 1                   |                |
| role_id    | int(10) unsigned | NO   |     | NULL                |                |
| bio        | text             | NO   |     | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| email      | varchar(64)      | NO   |     | NULL                |                |
+------------+------------------+------+-----+---------------------+----------------+
9 rows in set (0.00 sec)

Let’s undo the previous migration…

$ php artisan migrate:rollback
Rolled back: 2012_12_27_092602_add_email_to_authors_table

Check the database to confirm the field had been removed:

MariaDB [laravel_db]> DESCRIBE authors;
+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(11)          | NO   | PRI | NULL                | auto_increment |
| name       | varchar(255)     | NO   |     | NULL                |                |
| age        | int(11)          | YES  |     | NULL                |                |
| active     | tinyint(4)       | NO   |     | 1                   |                |
| role_id    | int(10) unsigned | NO   |     | NULL                |                |
| bio        | text             | NO   |     | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+------------+------------------+------+-----+---------------------+----------------+
8 rows in set (0.00 sec)

A little more time goes by and you realize that the email column should be unique. Two authors cannot have the same email address.

Let’s create another migration:

$ php artisan migrate:make set_email_to_unique_on_authors_table
Migration created successfully!

Let’s open the migration script and add the following code:

public function up()
{
    Schema::table('authors', function($t) {
        $t->unique('email', 'authors_email_unique');
    });
}

Since the “email” column already exists in our table, we simply create a new unique index on it by using the unique() method. The unique() method takes two parameters: the column name and a name for the index.

Now, let’s write the rollback code:

public function down()
{
        Schema::table('authors', function($t) {
                $t->dropUnique('authors_email_unique');
        });
}

As you can see, we use the dropUnique() method to remove the index we had created in the up() method.

It’s not yet possible to automatically roll-back to a point before a specific migration, so you’ll have to run the command repeatedly until you reach that migration. You can however reset all migrations that you’ve ever ran just by running:

$ php artisan migrate:reset

That’s it! I hope you find Laravel’s migrations a solution to avoid all the hours of pain you’ve gone through managing your database schema. For further information, please refer to the official Laravel documentation.

The following tables list additional Laravel Schema Builder methods that are available within a migration class:

Table-level Operations

Laravel Method Purpose
create() Create the table with a name. The second argument is a closure which should containc table definitions.
drop() Tables can be removed by using the drop() method call. As might be expected, removing a table also removes all of its columns and any indexes.
dropIfExists() Drops the table if it exists.
rename($to) Rename the table to a given name.

Column-level Operations

Laravel Method Purpose
dropColumn($columns) Indicate that the given columns should be dropped. Keep in mind that any index associated with that column will also be removed.
dropColumns() Indicate that the given columns should be dropped.

Index-level Operations

Laravel Method Purpose
primary($columns, $name = null) Specify the primary key(s) for the table.
unique($columns, $name = null) Specify a unique index for the table.
index($columns, $name = null) Specify an index for the table.
foreign($columns, $name = null) Specify a foreign key for the table.
dropPrimary($index = null) Indicate that the given primary key should be dropped.
dropUnique($index) Indicate that the given unique key should be dropped.
dropIndex($index) Indicate that the given index should be dropped.
dropForeign($index) Indicate that the given foreign key should be dropped.