Assignments Of Day 6 Laravel Database Migration

 Assignments Of Day 6

Laravel Database Migration

Assignment 1: Creating a Migration for a categories Table

Task:

Create a migration for a categories table with the following columns:

  • id (auto-increment primary key)
  • name (string)
  • description (text)
  • timestamps (created_at and updated_at)

Steps:

1.  Create the Migration: Open the terminal and run the following command to create a migration for the categories table:

bash

Copy code

php artisan make:migration create_categories_table

2.  Define the Table Structure: Open the newly created migration file in database/migrations (e.g., 2024_09_26_123456_create_categories_table.php), and define the structure of the categories table inside the up() method:

php

Copy code

public function up()

{

    Schema::create('categories', function (Blueprint $table) {

        $table->id(); // Creates an auto-incrementing primary key 'id' column

        $table->string('name'); // Creates a 'name' column of type VARCHAR

        $table->text('description'); // Creates a 'description' column of type TEXT

        $table->timestamps(); // Creates 'created_at' and 'updated_at' columns

    });

}

3.  Define the Rollback Action: In the down() method, specify that the categories table should be dropped when rolling back the migration:

php

Copy code

public function down()

{

    Schema::dropIfExists('categories');

}

4.  Run the Migration: Now, run the migration to create the categories table in the database:

bash

Copy code

php artisan migrate

5.  Verify the Table: Check your database (via phpMyAdmin or MySQL Workbench) to confirm that the categories table has been created with the correct columns.

Solution Explanation:

  • Schema::create('categories', function (Blueprint $table) { ... }): This method is used to define a new table called categories.
  • $table->id(): Creates an auto-incrementing primary key column named id.
  • $table->string('name'): Defines a column named name with a type of VARCHAR.
  • $table->text('description'): Defines a column named description with a type of TEXT for longer text values.
  • $table->timestamps(): Adds created_at and updated_at columns, automatically managed by Laravel.
  • The down() method drops the categories table if the migration is rolled back.

Assignment 2: Adding a Foreign Key Relationship

Task:

You already have a posts table and a users table. Add a foreign key column user_id to the posts table to link it to the users table. The foreign key should reference the id column of the users table.

Steps:

1.  Create a Migration to Add the Foreign Key: Run the following Artisan command to create a migration for adding the foreign key to the posts table:

bash

Copy code

php artisan make:migration add_user_id_to_posts_table

2.  Modify the Migration: Open the migration file created (e.g., 2024_09_26_123456_add_user_id_to_posts_table.php) and define the changes to be made to the posts table in the up() method:

php

Copy code

public function up()

{

    Schema::table('posts', function (Blueprint $table) {

        $table->unsignedBigInteger('user_id'); // Create an unsignedBigInteger column 'user_id'

        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); // Add a foreign key constraint

    });

}

3.  Define the Rollback Action: In the down() method, specify that the user_id column should be dropped if the migration is rolled back:

php

Copy code

public function down()

{

    Schema::table('posts', function (Blueprint $table) {

        $table->dropForeign(['user_id']); // Drop the foreign key constraint

        $table->dropColumn('user_id'); // Drop the 'user_id' column

    });

}

4.  Run the Migration: Run the migration to apply the changes to the database:

bash

Copy code

php artisan migrate

5.  Verify the Changes: After running the migration, check the posts table to ensure that the user_id column has been added and correctly linked to the users table.

Solution Explanation:

  • $table->unsignedBigInteger('user_id'): Adds an unsigned BIGINT column named user_id to the posts table. The unsigned modifier is used because foreign keys typically reference unsigned integer columns.
  • $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'): Defines a foreign key constraint. The user_id column references the id column in the users table, and if a user is deleted, all associated posts will also be deleted (onDelete('cascade')).
  • dropForeign(['user_id']) and dropColumn('user_id'): These methods drop the foreign key constraint and the user_id column when rolling back the migration.

Assignment 3: Rolling Back Migrations

Task:

You have added a categories table using a migration, but now you need to remove it. Roll back the migration to drop the categories table.

Steps:

1.  Rollback the Migration: To roll back the migration for dropping the categories table, run the following command:

bash

Copy code

php artisan migrate:rollback

2.  Verify the Table is Dropped: After running the rollback command, check your database to confirm that the categories table has been removed.

Solution Explanation:

  • php artisan migrate:rollback: This command will undo the last batch of migrations that were run. In this case, it will call the down() method of the create_categories_table migration, which will drop the categories table.

Assignment 4: Refreshing All Migrations

Task:

You want to reset your entire database schema (dropping all tables and then reapplying all migrations). Use the migrate:refresh command to reset and reapply all migrations.

Steps:

1.  Run the Refresh Command: Use the following command to refresh the migrations:

bash

Copy code

php artisan migrate:refresh

2.  Verify the Changes: After running the migrate:refresh command, check your database to confirm that all tables have been dropped and recreated according to the current migrations.

Solution Explanation:

  • php artisan migrate:refresh: This command will first rollback all migrations using migrate:rollback and then reapply all migrations from the beginning, essentially resetting the entire database schema. It is useful during development when you need to rebuild the schema from scratch.

Assignment 5: Creating a products Table with Constraints

Task:

Create a migration for a products table with the following columns:

  • id (auto-increment primary key)
  • name (string)
  • price (decimal)
  • category_id (foreign key referencing the id column in the categories table)
  • timestamps (created_at and updated_at)

Steps:

1.  Create the Migration: Use Artisan to generate the migration:

bash

Copy code

php artisan make:migration create_products_table

2.  Define the Table Structure: Open the migration file and define the structure of the products table:

php

Copy code

public function up()

{

    Schema::create('products', function (Blueprint $table) {

        $table->id(); // Creates an auto-incrementing primary key 'id' column

        $table->string('name'); // Creates a 'name' column of type VARCHAR

        $table->decimal('price', 8, 2); // Creates a 'price' column of type DECIMAL with 2 decimal places

        $table->unsignedBigInteger('category_id'); // Foreign key column for category

        $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade'); // Foreign key constraint

        $table->timestamps(); // Creates 'created_at' and 'updated_at' columns

    });

}

3.  Define the Rollback Action: In the down() method, specify that the products table should be dropped when rolling back the migration:

php

Copy code

public function down()

{

    Schema::dropIfExists('products');

}

4.  Run the Migration: Apply the migration:

bash

Copy code

php artisan migrate

5.  Verify the Table: Check your database to ensure the products table has been created with the specified columns and foreign key.

Solution Explanation:

  • $table->decimal('price', 8, 2): Defines a price column with a decimal type that can store values with up to 8 digits, 2 of which can be after the decimal point.
  • The foreign key relationship ensures that each product is associated with a valid category in the categories table.

Conclusion:

These assignments are designed to help students practice and understand key concepts of Laravel migrations, including table creation, foreign key relationships, rolling back migrations, and using schema builder methods. By completing these assignments, students will gain hands-on experience in managing and manipulating database schemas with Laravel migrations.

4o mini

You said:

give some more

ChatGPT said:

ChatGPT

Here are some additional Laravel Database Migration assignments with step-by-step solutions and explanations for further practice:

Assignment 6: Creating a users Table with Validation Constraints

Task:

Create a migration for a users table with the following columns:

  • id (auto-increment primary key)
  • name (string, required)
  • email (string, unique, required)
  • password (string, required)
  • role (enum with possible values: 'admin', 'user')
  • timestamps (created_at and updated_at)

Steps:

1.  Create the Migration: Create a migration for the users table:

bash

Copy code

php artisan make:migration create_users_table

2.  Define the Table Structure: Open the migration file and define the structure of the users table in the up() method:

php

Copy code

public function up()

{

    Schema::create('users', function (Blueprint $table) {

        $table->id(); // Auto-incrementing 'id' column

        $table->string('name'); // 'name' column (VARCHAR)

        $table->string('email')->unique(); // 'email' column with unique constraint

        $table->string('password'); // 'password' column (VARCHAR)

        $table->enum('role', ['admin', 'user']); // 'role' column with enum values

        $table->timestamps(); // 'created_at' and 'updated_at' columns

    });

}

3.  Define the Rollback Action: In the down() method, specify that the users table should be dropped when rolling back:

php

Copy code

public function down()

{

    Schema::dropIfExists('users');

}

4.  Run the Migration: Run the migration to apply the changes:

bash

Copy code

php artisan migrate

5.  Verify the Table: After running the migration, check your database to ensure the users table has been created with the correct columns and constraints.

Solution Explanation:

  • $table->string('email')->unique(): Ensures that all email addresses in the users table are unique.
  • $table->enum('role', ['admin', 'user']): Defines an enum column for the role attribute, limiting values to either 'admin' or 'user'.
  • The timestamps() method adds the created_at and updated_at columns, which are automatically managed by Laravel.

Assignment 7: Adding a Unique Index to an Existing Column

Task:

You have an existing users table, but you need to ensure that the email column is unique. Write a migration to add a unique index to the email column.

Steps:

1.  Create the Migration: Use the following Artisan command to create a migration that adds a unique index to the email column:

bash

Copy code

php artisan make:migration add_unique_index_to_email_column

2.  Modify the Migration: Open the migration file and modify it to add a unique index to the email column in the up() method:

php

Copy code

public function up()

{

    Schema::table('users', function (Blueprint $table) {

        $table->unique('email'); // Adds a unique index to the 'email' column

    });

}

3.  Define the Rollback Action: In the down() method, specify that the unique index on the email column should be removed when rolling back:

php

Copy code

public function down()

{

    Schema::table('users', function (Blueprint $table) {

        $table->dropUnique(['email']); // Removes the unique index from the 'email' column

    });

}

4.  Run the Migration: Apply the migration to add the unique index:

bash

Copy code

php artisan migrate

5.  Verify the Change: After running the migration, check the database schema to confirm that the unique index has been added to the email column.

Solution Explanation:

  • $table->unique('email'): Adds a unique constraint on the email column, ensuring that no two records can have the same email address.
  • The down() method uses $table->dropUnique(['email']) to remove the unique index when the migration is rolled back.

Assignment 8: Creating a Pivot Table for Many-to-Many Relationship

Task:

You are building a blog platform where posts and tags have a many-to-many relationship. Create a pivot table called post_tag to link posts and tags.

The post_tag table should have:

  • post_id (foreign key referencing the id column in the posts table)
  • tag_id (foreign key referencing the id column in the tags table)

Steps:

1.  Create the Migration: Generate the migration for the post_tag pivot table:

bash

Copy code

php artisan make:migration create_post_tag_table

2.  Define the Table Structure: Open the migration file and define the post_tag pivot table structure in the up() method:

php

Copy code

public function up()

{

    Schema::create('post_tag', function (Blueprint $table) {

        $table->id();

        $table->unsignedBigInteger('post_id'); // Foreign key to the 'posts' table

        $table->unsignedBigInteger('tag_id'); // Foreign key to the 'tags' table

        $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade'); // Foreign key constraint for 'post_id'

        $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade'); // Foreign key constraint for 'tag_id'

        $table->timestamps(); // 'created_at' and 'updated_at' columns

    });

}

3.  Define the Rollback Action: In the down() method, specify that the post_tag table should be dropped when rolling back:

php

Copy code

public function down()

{

    Schema::dropIfExists('post_tag');

}

4.  Run the Migration: Apply the migration to create the pivot table:

bash

Copy code

php artisan migrate

5.  Verify the Table: After running the migration, check the database to confirm that the post_tag table has been created with the correct foreign keys.

Solution Explanation:

  • The post_tag table is a pivot table that links the posts and tags tables in a many-to-many relationship.
  • The unsignedBigInteger type is used for foreign keys to ensure the IDs match the default type of auto-incrementing primary keys in Laravel.
  • The onDelete('cascade') ensures that if a post or tag is deleted, the associated entries in the pivot table will also be removed.

Assignment 9: Changing a Column's Data Type

Task:

In the products table, you need to change the data type of the price column from decimal to float (with two decimal points). Write a migration to modify the price column.

Steps:

1.  Create the Migration: Use Artisan to create a migration for modifying the column:

bash

Copy code

php artisan make:migration change_price_column_in_products_table

2.  Modify the Column: Open the migration file and modify the price column in the up() method:

php

Copy code

public function up()

{

    Schema::table('products', function (Blueprint $table) {

        $table->float('price', 8, 2)->change(); // Changes the 'price' column to type float with 2 decimal points

    });

}

3.  Define the Rollback Action: In the down() method, specify how to revert the column back to decimal if the migration is rolled back:

php

Copy code

public function down()

{

    Schema::table('products', function (Blueprint $table) {

        $table->decimal('price', 8, 2)->change(); // Changes 'price' back to decimal

    });

}

4.  Run the Migration: Run the migration to apply the changes:

bash

Copy code

php artisan migrate

5.  Verify the Change: After running the migration, check your database schema to ensure that the price column type has been changed from decimal to float.

Solution Explanation:

  • $table->float('price', 8, 2)->change(): This changes the price column to a float with 8 digits, 2 of which are after the decimal point.
  • The down() method reverts the column to decimal(8, 2) in case of a rollback.

Assignment 10: Creating a orders Table with Multiple Foreign Keys

Task:

Create a migration for an orders table with the following columns:

  • id (auto-increment primary key)
  • user_id (foreign key to the users table)
  • product_id (foreign key to the products table)
  • quantity (integer)
  • status (enum with values 'pending', 'completed', 'cancelled')
  • timestamps (created_at and updated_at)

Solution:

1.  Create Migration:

bash

Copy code

php artisan make:migration create_orders_table

2.  Define Structure:

php

Copy code

public function up()

{

    Schema::create('orders', function (Blueprint $table) {

        $table->id();

        $table->unsignedBigInteger('user_id');

        $table->unsignedBigInteger('product_id');

        $table->integer('quantity');

        $table->enum('status', ['pending', 'completed', 'cancelled']);

        $table->timestamps();

 

        // Foreign Key Constraints

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

        $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');

    });

}

3.  Rollback:

php

Copy code

public function down()

{

    Schema::dropIfExists('orders');

}

Conclusion:

These assignments help reinforce key concepts of Laravel Migrations, focusing on creating tables, adding relationships, modifying columns, and handling foreign keys. 


Comments

Post a Comment

Popular posts from this blog

MCQs Of Day CRUD 8 Operations with Eloquent

MCQs Of Day 8 CRUD  Operations with Eloquent   CRUD Operations with Eloquent - MCQs 1. What does CRUD stand for in database management? a) Create, Read, Update, Delete b) Connect, Retrieve, Update, Delete c) Create, Return, Update, Delete d) Collect, Read, Update, Delete Answer:  a) Create, Read, Update, Delete Explanation:  CRUD is a set of operations that stand for Create, Read, Update, and Delete, which are basic functions used in databases to manage records. 2. Which Laravel method is used to create a new record in a database? a) create() b) store() c) insert() d) add() Answer:  a) create() Explanation:  The create() method is used in Laravel's Eloquent ORM to create a new record in the database. 3. Which of the following is true about Eloquent ORM in Laravel? a) It allows only raw SQL queries. b) It does not support relationships between models. c) It provides an active record implementation for working with the database. d) It is used only for Read op...
Read more

Lecture Notes Of Day 24: Laravel Policies

  Lecture Notes Of Day 24: Laravel Policies Objective: To implement policies for authorization, ensuring that different users can perform only the actions they are authorized to do. This will help students understand how to manage user permissions effectively for different actions in Laravel applications. Outcome: By the end of this class, students will be able to create, register, and use policies to manage user permissions for various actions in a Laravel application. They will also learn how to secure resources and actions based on user roles or other attributes. What are Policies in Laravel? In Laravel, policies provide a convenient way to authorize user actions on certain resources. A policy is essentially a class that organizes authorization logic for a particular model. For example, if you have a Post model, you might have a PostPolicy class that defines who can view, update, or delete a post. Why use Policies? Policies centralize authorization ...
Read more

MCQs of Day 7 Eloquent ORM Basics

  MCQs of Day 7 Eloquent ORM Basics 1. Which command is used to create an Eloquent model in Laravel? A) php artisan create:model B) php artisan make:model C) php artisan model:create D) php artisan generate:model Answer: B) php artisan make:model Explanation: The php artisan make:model command is used to create a new Eloquent model in Laravel. 2. What is the purpose of the $fillable property in an Eloquent model? A) To define which fields can be mass-assigned B) To define the table name C) To store timestamps D) To define relationships between models Answer: A) To define which fields can be mass-assigned Explanation: The $fillable property in an Eloquent model defines which fields are allowed for mass-assignment to protect against mass-assignment vulnerabilities. 3. How do you retrieve a single record by its ID in Eloquent? A) Model::get(1) B) Model::find(1) C) Model::single(1) D) Model::first(1) Answer: B) Model::find(1) Explanation: The find() method retrieves a single record by ...
Read more