Modifying tables

Learn how to alter existing tables

This version of the library is no longer maintained. Please consider upgrading to the latest release

Alter tables

You can alter existing tables by using the alter method. This method takes two arguments: the first argument represents the name of the table you want to modify and the second argument is an anonymous callback function (Closure) that takes as an argument an instance of Opis\Database\Schema\AlterTable class.

$db->schema()->alter('users', function($table){
    
    //code
    
}));

Adding columns

Adding a new column to an existing table is done in a similar way described in the Creating tables section. The only difference is that you won’t be able to directly add a constraint or an index.

$db->schema()->alter('users', function($table){
    
    $table->integer('age')->size('small')->unsigned();
    
}));

Deleting columns

You can delete a column by using the dropColumn method. The method accepts a single argument representing the column’s name.

$db->schema()->alter('users', function($table){
    
    $table->dropColumn('age');
    
}));

Renaming columns

Renaming a column is done using the renameColumn method. This method takes as arguments the current and the new name of the column.

$db->schema()->alter('users', function($table){
    
    $table->renameColumn('name', 'username');
    
}));

Adding default values

You can add a default value for a column by using the setDefaultValue method. This method takes as arguments the name of the column and the default value for that column.

$db->schema()->alter('users', function($table){
    
    $table->setDefaultValue('age', 18);
    
}));

Removing default values

You can remove a default value of a column by using the dropDefaultValue method. This method takes as an argument the name of the column.

$db->schema()->alter('users', function($table){
    
    $table->dropDefaultValue('age');
    
}));

Add primary key

You may add the primary key by calling the primary method.

$db->schema()->alter('users', function($table){
    
    $table->primary('age');
    
}));

Delete primary key

Dropping primary key is done by using the dropPrimary method. This method takes a single argument representing the name of the primary key.

$db->schema()->alter('users', function($table){
    
    $table->dropPrimary('id');
    
}));

Add unique keys

You may add a unique key by calling the unique method.

$db->schema()->alter('users', function($table){
    
    $table->unique('email');
    
}));

Delete unique keys

Dropping a unique key is done by using the dropUnique method. This method takes a single argument representing the name of the unique key you want to delete.

$db->schema()->alter('users', function($table){
    
    $table->dropUnique('email');
    
}));

Add foreign keys

You may add a foreign key by using the foreign method.

$db->schema()->alter('users', function($table){

    $table->foreign('profile_id')->references('profiles')->on('id');

}));

Delete foreign keys

Dropping a foreign key is done by using the dropForeign method. This method takes a single argument representing the name of the foreign key you want to delete.

$db->schema()->alter('users', function($table){
    
    $table->dropForeign('fk_profile');
    
}));

Add indexes

You can add an index by calling the index method.

$db->schema()->alter('users', function($table){
    
    $table->index('username');
    
}));

Delete indexes

Dropping an index is done by using the dropIndex method. This method takes a single argument representing the name of the index you want to remove.

$db->schema()->alter('users', function($table){
    
    $table->dropIndex('username');
    
}));

Changing a column’s type

When a table is altered you may change the types of its columns by using the following methods: toInteger, toFloat, toDouble, toDecimal, toBoolean, toBinary, toString, toFixed, toText, toTime, toTimestamp, toDate, toDateTime.

$db->schema()->alter('users', function($table){
    $table->toDouble('height');
}));

Truncate tables

You may use the truncate method if you want to remove all rows from a table.

$db->schema()->truncate('users');

Rename tables

Renaming a table is done by using the renameTable method. This method takes as arguments the current name of the table and the new name for that table.

//Rename table `user` to `users` 
$db->schema()->renameTable('user', 'users');

Drop tables

You may use the drop method if you want to delete a table.

$db->schema()->drop('users');