Schema Builder

Contents

The Basics

The Schema Builder provides methods for creating and modifying your database tables. Using a fluent syntax, you can work with your tables without using any vendor specific SQL.

Further Reading:

Creating & Dropping Tables

The Schema class is used to create and modify tables. Let's jump right into an example:

Creating a simple database table:

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

Let's go over this example. The create method tells the Schema builder that this is a new table, so it should be created. In the second argument, we passed a Closure which receives a Table instance. Using this Table object, we can fluently add and drop columns and indexes on the table.

Dropping a table from the database:

Schema::drop('users');

Dropping a table from a given database connection:

Schema::drop('users', 'connection_name');

Sometimes you may need to specify the database connection on which the schema operation should be performed.

Specifying the connection to run the operation on:

Schema::create('users', function($table)
{
    $table->on('connection');
});

Adding Columns

The fluent table builder's methods allow you to add columns without using vendor specific SQL. Let's go over it's methods:

Command Description
$table->increments('id'); Incrementing ID to the table
$table->string('email'); VARCHAR equivalent column
$table->string('name', 100); VARCHAR equivalent with a length
$table->integer('votes'); INTEGER equivalent to the table
$table->float('amount'); FLOAT equivalent to the table
$table->decimal('amount', 5, 2); DECIMAL equivalent with a precision and scale
$table->boolean('confirmed'); BOOLEAN equivalent to the table
$table->date('created_at'); DATE equivalent to the table
$table->timestamp('added_on'); TIMESTAMP equivalent to the table
$table->timestamps(); Adds created_at and updated_at columns
$table->text('description'); TEXT equivalent to the table
$table->blob('data'); BLOB equivalent to the table
->nullable() Designate that the column allows NULL values
->default($value) Declare a default value for a column
->unsigned() Set INTEGER to UNSIGNED

Note: Laravel's "boolean" type maps to a small integer column on all database systems.

Example of creating a table and adding columns

Schema::table('users', function($table)
{
    $table->create();
    $table->increments('id');
    $table->string('username');
    $table->string('email');
    $table->string('phone')->nullable();
    $table->text('about');
    $table->timestamps();
});

Dropping Columns

Dropping a column from a database table:

$table->drop_column('name');

Dropping several columns from a database table:

$table->drop_column(array('name', 'email'));

Adding Indexes

The Schema builder supports several types of indexes. There are two ways to add the indexes. Each type of index has its method; however, you can also fluently define an index on the same line as a column addition. Let's take a look:

Fluently creating a string column with an index:

$table->string('email')->unique();

If defining the indexes on a separate line is more your style, here are example of using each of the index methods:

Command Description
$table->primary('id'); Adding a primary key
$table->primary(array('fname', 'lname')); Adding composite keys
$table->unique('email'); Adding a unique index
$table->fulltext('description'); Adding a full-text index
$table->index('state'); Adding a basic index

Dropping Indexes

To drop indexes you must specify the index's name. Laravel assigns a reasonable name to all indexes. Simply concatenate the table name and the names of the columns in the index, then append the type of the index. Let's take a look at some examples:

Command Description
$table->drop_primary('users_id_primary'); Dropping a primary key from the "users" table
$table->drop_unique('users_email_unique'); Dropping a unique index from the "users" table
$table->drop_fulltext('profile_description_fulltext'); Dropping a full-text index from the "profile" table
$table->drop_index('geo_state_index'); Dropping a basic index from the "geo" table

Foreign Keys

You may easily add foreign key constraints to your table using Schema's fluent interface. For example, let's assume you have a user_id on a posts table, which references the id column of the users table. Here's how to add a foreign key constraint for the column:

$table->foreign('user_id')->references('id')->on('users');

You may also specify options for the "on delete" and "on update" actions of the foreign key:

$table->foreign('user_id')->references('id')->on('users')->on_delete('restrict');

$table->foreign('user_id')->references('id')->on('users')->on_update('cascade');

You may also easily drop a foreign key constraint. The default foreign key names follow the same convention as the other indexes created by the Schema builder. Here's an example:

$table->drop_foreign('posts_user_id_foreign');

Note: The field referenced in the foreign key is very likely an auto increment and therefore automatically an unsigned integer. Please make sure to create the foreign key field with unsigned() as both fields have to be the exact same type, the engine on both tables has to be set to InnoDB, and the referenced table must be created before the table with the foreign key.

$table->engine = 'InnoDB';

$table->integer('user_id')->unsigned();