Andrew Dorokhov
Tables
Creating a table with an explicit charset and collation — pass them as the third argument:
use yii\db\Migration;
class m181018_094305_add_probes_table extends Migration
{
public function up()
{
$this->createTable('probes', [
'id' => $this->primaryKey(),
'query_keyword' => $this->string()->notNull(),
'creation_date' => $this->date()->notNull(),
'state' => $this->tinyInteger()->defaultValue(1),
], 'CHARACTER SET utf8 COLLATE utf8_unicode_ci');
}
public function down()
{
$this->dropTable('probes');
}
}
Rows
Inserting data:
$this->insert('exchange', [
'name' => 'Yobit.net',
]);
Columns
Adding a column:
$this->addColumn('job', 'score', $this->integer()->after('starred'));
Removing a column:
$this->dropColumn('job', 'score');
Index
We can name it like idx-TABLE_NAME-FIELD_NAME.
Add index:
$this->createIndex(
'idx-post-category_id',
'post',
'category_id'
);
Delete index:
$this->dropIndex(
'idx-post-category_id',
'post'
);
Foreign keys
Terminology: the parent is the table we reference, the child is the table where the relation is declared.
$this->addForeignKey(
'fk-post-author_id', // Key name. A handy format is fk-childTable-fieldName.
'post', // Child table.
'author_id', // Field of the child table.
'user', // Parent table.
'id', // Field of the parent table.
'CASCADE', // ON DELETE action.
'CASCADE' // ON UPDATE action.
);
Actions:
ON DELETE CASCADE— when the parent row is deleted, the child row is deleted too.ON UPDATE CASCADE— when the parent ID changes, the referencing IDs in the child table are updated.
Another example:
$this->addForeignKey(
'fk-job-category_id',
'job',
'category_id',
'category',
'id',
'CASCADE',
'CASCADE'
);