Before renaming a
table, we should verify that foreign key constraints on the table have an
explicit name in our migration files.
Otherwise, the foreign
key constraint name refers to the old table name.
Columns
Creating
Columns
The table method is used to update existing tables.
This method has similar
arguments like the create method.
The two arguments that are
used for creating columns are the name of the table and closure, which receives a Blueprint
instance that we use for adding columns to the table.
Syntax:
Schema::table('users', function (Blueprint $table) {
$table->string('email');
});
Column
Types:
The schema builder contains various column
types, which defines a building of our tables:
Command
Description
$table->bigIncrements('id');
The Auto-incrementing UNSIGNED BIGINT (primary
key) equivalent column.
$table->bigInteger('votes');
It
BIGINT equivalent column.
$table->binary('data');
It BLOB equivalent column.
$table->boolean('confirmed');
It
BOOLEAN equivalent column.
$table->char('name', 50);
The CHAR command equivalent column with an
optional length.
$table->date('created_at');
DATE
equivalent column.
$table->dateTime('created_at');
DATETIME equivalent column.
$table->dateTimeTz('created_at');
DATETIME
(with timezone) equivalent column.
$table->decimal('amount', 15, 12);
DECIMAL equivalent column with a combination of (total
digits) and scale (decimal digits).
$table->double('amount', 15, 12);
DOUBLE
equivalent column with a combination of (total digits) and scale (decimal
digits).
$table->enum('level', ['easy', 'hard']);
ENUM equivalent column.
$table->float('amount', 18, 12);
FLOAT
equivalent column with a combination of (total digits) and scale (decimal
digits).
UNSIGNED DECIMAL equivalent column with a combination
of (total digits) and scale (decimal digits).
$table->unsignedInteger('votes');
UNSIGNED
INTEGER equivalent column.
$table->unsignedMediumInteger('votes');
UNSIGNED MEDIUMINT equivalent column.
$table->unsignedSmallInteger('votes');
UNSIGNED
SMALLINT equivalent column.
$table->unsignedTinyInteger('votes');
UNSIGNED TINYINT equivalent column.
$table->uuid('id');
UUID
equivalent column.
$table->year('birth_year');
YEAR equivalent column.
Modifying Columns
We
have to add the doctrine/dbal
dependency to our composer.json before modifying a column.
The
DBAL library is used to determine the current state of the column, and it will
create the SQL queries that are needed to make the specified adjustments to the
column.
composer require doctrine/dbal
Updating Column Attributes
The
change method allows us to modify the existing column types to a new type or
modify the column`s attributes.
The change
method in action increases the size of the name column from 25 to 50.
Syntax:
Schema::table('users', function (Blueprint $table)
{
$table->string('name', value)->change();
});
Example:
Schema::table('users1', function (Blueprint $table)
{
$table->string('employee', 20)->change();
});
We also modify a column to be nullable:
Syntax:
Schema::table('users', function (Blueprint $table)
{
$table->string('name', value)->nullable()->change();
});
Example:
Schema::table('users', function (Blueprint $table)
{
$table->string('employee', 20)->nullable()->change();
});
Renaming
Columns
The renameColumn
method is used to rename a column on the Schema builder.
Add the doctrine/dbal dependency before renaming a column to composer.json file.
Syntax:
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('from', 'to');
});
Example:
Schema::table('users', function (Blueprint $table) {
$table->renameColumn('roll-on', 'batch');
});
Renaming a
column, which also has a column type enum is not currently supported.
Dropping Columns
The dropColumn method is used to drop a column.
We need to add the doctrine/dbal dependency into our composer.json file, and it
will run the composer
update command in our terminal to install the library.
Schema::table('users', function (Blueprint $table) {
$table->dropColumn('votes');
});
To drop multiple columns from a table, we use
the dropColumn
method by passing an array of column names.
It drop
the morphable_id and morphable_type columns.
$table->dropRememberToken();
It drop the remember_token column.
$table->dropSoftDeletes();
It drop
the deleted_at column.
$table->dropSoftDeletesTz();
Alias of dropSoftDeletes() method.
$table->dropTimestamps();
It drop
the created_at and updated_at columns.
$table->dropTimestampsTz();
Alias of dropTimestamps() method.
INDEX
Creating
Index
For creating the index, we use the unique
method onto the column definition
Syntax:
$table->string(‘name’)->unique();
Otherwise, we create the index after defining
the column
Example:-
$table->unique('name');
We can create an array of columns for an
index method to create a compound index:
$table->index([‘employee_id’, ‘created_at’]);
Laravel will automatically generate the index
name according to their name, but we can pass a second argument by specifying the name
$table->unique('name', 'unique_name');
Available
Index Types
Each of
the index methods accepts a second argument to define the name of the index.
If it
omits, the name will be taken from the names of the table and columns(s).
Command
Description
$table->primary('id');
Adds a primary key.
$table->primary(['id',
'parent_id']);
Adds composite keys.
$table->unique('email');
Adds a unique index.
$table->index('state');
Adds a plain index.
$table->spatialIndex('location');
Adds a spatial index. (except SQLite)
Index Lengths & MySQL/Maria DB
By
default, the Laravel uses the utf8mb4 character set that
includes the support for storing the “emojis” in the database.
If
we want to run the older version of the MySQL or Maria DB, we need to manually
configure the default string length that is generated by migrations. It is for
MySQL in order to create an index for them.
We
configure this by calling the Schema::defaultStringLength method into our AppServiceProvider:
use Illuminate\Support\Facades\Schema;
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
Schema::defaultStringLength(191);
}
We enable the innodb_large_prefix
option for our database.
Renaming & Dropping Index
The renameIndex method is used to rename an index. The renameIndex
method accepts the current index name as its first argument and the desired
name as its second argument:
$table->renameIndex(‘from’, ‘to)
For dropping an index, we must specify the
index`s name.
By default, the Laravel will automatically
assign a reasonable name to the index.
It drop a spatial index from the "geo"
table (except SQLite).
We
pass an array of columns into the method, which drop the index. The similar
index name will be generated according of the table name, columns and key type:
Schema::table(‘posts’, function (Blueprint $table)
{
$table->unsignedBigInteger(‘user_id’);
$table->foreign(‘user_id’)->references(‘id’)->on(‘users’);
});
We also specify the desired action for “on
delete” and “on update” properties of the constraint:
Schema::table('geo', function (Blueprint $table)
{
$table->dropIndex(['state']);
// Drops index 'geo_state_index'
});
Foreign Key Constraints
Laravel provides support for creating foreign
key constraints that are used to force referential integrity at the database
level.
For
example:
A user_id column of the posts table, refers to the id column on a user’s table:
Schema::table('posts', function (Blueprint $table) {
$table->unsignedBigInteger('user_id');
$table->foreign('user_id')->references('id')->on('users');
});
We also define the required action for the “on delete” and “on update” properties of
the constraint:
By
default, the SQLite disables foreign key constraints. While using SQLite, make
sure to enable foreign key support in our database configuration before
attempting to create them in our migrations.
