CODEWITHSUNDEEP
phpmysqllaraveldatabase-migration
Illia Yaremchuk
Illia Yaremchuk

Reputation: 2025

Create laravel migration file from MySQL dump

There is a MySQL request to create a table:

CREATE TABLE IF NOT EXISTS `tb_edited_message` (
  `id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for this entry',
  `chat_id` bigint COMMENT 'Unique chat identifier',
  `message_id` bigint UNSIGNED COMMENT 'Unique message identifier',
  `user_id` bigint NULL COMMENT 'Unique user identifier',
  `edit_date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was edited in timestamp format',
  `text` TEXT COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8',
  `entities` TEXT COMMENT 'For text messages, special entities like usernames, URLs, bot commands, etc. that appear in the text',
  `caption` TEXT COMMENT  'For message with caption, the actual UTF-8 text of the caption',

  PRIMARY KEY (`id`),
  KEY `chat_id` (`chat_id`),
  KEY `message_id` (`message_id`),
  KEY `user_id` (`user_id`),

  FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
  FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `tb_message` (`chat_id`, `id`),
  FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

I created a migration:

...
public function up()
    {
        Schema::create('tb_edited_message', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_520_ci';

            $table->bigIncrements('id');
            $table->unsignedBigInteger('chat_id')->index();
            $table->unsignedBigInteger('message_id')->index();
            $table->unsignedBigInteger('user_id')->index();
            $table->timestamp('edit_date')->nullable();
            $table->text('text')->nullable();
            $table->text('entities')->nullable();
            $table->text('caption')->nullable();
        });

        Schema::table('tb_edited_message', function($table) {
            $table->foreign('chat_id')->references('id')->on('tb_chat');
            $table->foreign('chat_id', 'message_id')->references('chat_id', 'id')->on('tb_message');
            $table->foreign('user_id')->references('id')->on('tb_user');
        });
    }
...

But after start php artisan migrate: enter image description here

Error in line: $table->foreign('chat_id', 'message_id')->references('chat_id', 'id')->on('tb_message');

Tables tb_chat AND tb_user AND tb_message This tables were created before tb_edited_message

....
public function up()
    {
        Schema::create('tb_user', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_520_ci';

            $table->bigIncrements('id');
            $table->boolean('is_bot')->default(false);
            $table->char('first_name', 255)->default('');
            $table->char('last_name', 255)->nullable();
            $table->char('username', 255)->nullable();
            $table->char('language_code', 10)->nullable();
            $table->timestamps();

            $table->index('username');
        });
    }
....

....
public function up()
    {
        Schema::create('tb_chat', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_520_ci';

            $table->bigIncrements('id');
            $table->bigInteger('old_id')->nullable();
            $table->enum('type', ['private', 'group', 'supergroup', 'channel']);
            $table->char('title', 255)->nullable();
            $table->char('username', 255)->nullable();
            $table->boolean('all_members_are_administrators')->default(false);
            $table->timestamps();

            $table->index('old_id');
        });
    }
....

.....
public function up()
    {
        Schema::create('tb_message', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->charset = 'utf8mb4';
            $table->collation = 'utf8mb4_unicode_520_ci';

            $table->unsignedBigInteger('chat_id');
            $table->unsignedBigInteger('id');
            $table->unsignedBigInteger('user_id')->nullable()->index();
            $table->timestamp('date')->nullable();
            $table->unsignedBigInteger('forward_from')->nullable()->index();
            $table->unsignedBigInteger('forward_from_chat')->nullable()->index();
            $table->unsignedBigInteger('forward_from_message_id')->nullable();
            $table->timestamp('forward_date')->nullable();
            $table->unsignedBigInteger('reply_to_chat')->nullable()->index();
            $table->unsignedBigInteger('reply_to_message')->nullable()->index();
            $table->text('media_group_id')->nullable();

            $table->text('text')->nullable();
            $table->text('entities')->nullable();
            $table->text('audio')->nullable();
            $table->text('document')->nullable();
            $table->text('photo')->nullable();
            $table->text('sticker')->nullable();
            $table->text('video')->nullable();
            $table->text('voice')->nullable();
            $table->text('video_note')->nullable();
            $table->text('contact')->nullable();
            $table->text('location')->nullable();
            $table->text('venue')->nullable();
            $table->text('caption')->nullable();
            $table->text('new_chat_members')->nullable();

            $table->unsignedBigInteger('left_chat_member')->nullable()->index();

            $table->char('new_chat_title', 255)->nullable();
            $table->text('new_chat_photo')->nullable();

            $table->boolean('delete_chat_photo')->default(false);
            $table->boolean('group_chat_created')->default(false);
            $table->boolean('supergroup_chat_created')->default(false);
            $table->boolean('channel_chat_created')->default(false);

            $table->unsignedBigInteger('migrate_to_chat_id')->nullable()->index();
            $table->unsignedBigInteger('migrate_from_chat_id')->nullable()->index();

            $table->text('pinned_message')->nullable();

            $table->primary(['chat_id', 'id']);
        });

        Schema::table('tb_message', function($table) {
            $table->foreign('user_id')->references('id')->on('tb_user');
            $table->foreign('chat_id')->references('id')->on('tb_chat');
            $table->foreign('forward_from')->references('id')->on('tb_user');
            $table->foreign('forward_from_chat')->references('id')->on('tb_chat');
            $table->foreign('reply_to_chat', 'reply_to_message')->references('chat_id', 'id')->on('tb_message');
            $table->foreign('left_chat_member')->references('id')->on('tb_user');
        });
    }
.....

This migrations for TelegramBot - which so far without the support of Laravel (no migrations)

Сreate migrations for these tables

Upvotes: 0

Views: 497

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31802

From https://github.com/laravel/framework/blob/5.5/src/Illuminate/Database/Schema/Blueprint.php#L418

/**
 * Specify a foreign key for the table.
 *
 * @param  string|array  $columns
 * @param  string  $name
 * @return \Illuminate\Support\Fluent
 */
public function foreign($columns, $name = null)
{
    return $this->indexCommand('foreign', $columns, $name);
}

We can see that the first parameter is the column or an array of columns. The second parameter seams to be the (optional) constraint name. (Though your error message suggests that when two parameters are used, the first one is the constraint name and the second is the column name or an array of column names.)

So you should wrap the column names into an array:

$table->foreign(['chat_id', 'message_id'])->references(['chat_id', 'id'])->on('tb_message');

Upvotes: 1

Anne W.
Anne W.

Reputation: 68

I had a similar issue due to the tables I was referencing not yet existing. What I did was I loaded all the tables first without foreign key constraints in one migration. Then in a second migration I added all the foreign key constraints to each table.

Upvotes: 0

Related Questions