Laravel Database: Migrations, Tables , Indexes

1. make file of migrations
	php artisan make:migration create_users_table

2. with table
	php artisan make:migration create_users_table --create=users
	php artisan make:migration add_votes_to_users_table --table=users

3. up & down method
	public function up()
    {
        Schema::create('flights', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('flights');
    }

4. Run migrations
	php artisan migrate
	php artisan migrate --force   (force to lose data)

5. Rollback (This command rolls back the last "batch" of migrations)
	php artisan migrate:rollback

	//last 5 migration rollback
	php artisan migrate:rollback --step=5

	//all migration rollback
	php artisan migrate:reset

	//Rollback & Migrate In Single Command
	(The migrate:refresh command will roll back all of your migrations and then execute the migrate command. This command effectively re-creates your entire database:)

	php artisan migrate:refresh

	// Refresh the database and run all database seeds...
	php artisan migrate:refresh --seed

	//You may rollback & re-migrate a limited number of migrations by providing the step
	php artisan migrate:refresh --step=5

	//Drop All Tables & Migrate
	//The migrate:fresh command will drop all tables from the database and then execute the migrate command:

	php artisan migrate:fresh


******** Tables **********

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

	2.Checking For Table / Column Existence
		if (Schema::hasTable('users')) {
			......
		}

		if (Schema::hasColumn('users', 'email')) {
			.....
		}

		//Rename - droppping table
		Schema::rename($from, $to);

		//drop
		Schema::drop('users');
		Schema::dropIfExists('users');

		//creating column name
		https://laravel.com/docs/5.6/migrations#creating-columns

			$table->rememberToken();   -> Adds a nullable remember_token VARCHAR(100) equivalent column.
			$table->softDeletes();	->Adds a nullable deleted_at TIMESTAMP equivalent column for soft deletes.
			$table->timestamp('added_on');

	2. column modifires
		Schema::table('users', function (Blueprint $table) {
		    $table->string('email')->nullable();
		});

		->after('column')
		->autoIncrement()
		->default($value)
		->first() //Place the column "first" in the table (MySQL)
		->nullable($value = true)
		->unsigned()
		->useCurrent()

	3. Modifying columns
		//Before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file
		composer require doctrine/dbal

		//change 
			Schema::table('users', function (Blueprint $table) {
			    $table->string('name', 50)->change();
			});
			Schema::table('users', function (Blueprint $table) {
			    $table->string('name', 50)->nullable()->change();
			});

		//rename column
			Schema::table('users', function (Blueprint $table) {
			    $table->renameColumn('from', 'to');
			});


		//drop column
			Schema::table('users', function (Blueprint $table) {
			    $table->dropColumn('votes');
			});

		//drop multiple column
			Schema::table('users', function (Blueprint $table) {
			    $table->dropColumn(['votes', 'avatar', 'location']);
			});

		$table->dropRememberToken();
		$table->dropSoftDeletes();
		$table->dropTimestamps(); //Drop the created_at and updated_at columns.

	//indexes
		1. unique
			$table->string('email')->unique();
			or 
			$table->unique('email');

		2. composite
			$table->index(['account_id', 'created_at']);

		3. other
			$table->primary('id');
			$table->primary(['id', 'parent_id']);
			$table->unique('email');	//Adds a unique index.
			$table->index('state'); 	//Adds a plain index.

		4. Index Lengths & MySQL / MariaDB
			//before 5.77
				AppServiceProvider -> define
					public function boot()
					{
					    Schema::defaultStringLength(191);
					}

		5. Rename index
			$table->renameIndex('from', 'to')


	//Foreign Key Constraints
		1. foreign key
			Schema::table('posts', function (Blueprint $table) {
			    $table->unsignedInteger('user_id');

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

		2. ondelete & on update
			$table->foreign('user_id')
			      ->references('id')->on('users')
			      ->onDelete('cascade');

		3. drop foreign key
			$table->dropForeign('posts_user_id_foreign');
			or
			$table->dropForeign(['user_id']);