Can’t do PHP Artisan Migrate with SQLSTATE [42000]

I just want to PHP Artisan Migrate, I’ve just completed my table on migration but have error

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'modified_date' 
(SQL: create table `dso` (`id` bigint unsigned not null auto_increment primary key, 
`id_dso` bigint unsigned not null, `id_rso` bigint unsigned not null, 
`id_focus` bigint unsigned not null, `id_wilayah` bigint unsigned not null, `id_grup_wilayah` bigint unsigned not null, 
`nama_dso` varchar(255) not null, `created_by` varchar(255) not null, 
`created_date` timestamp not null, `modified_by` varchar(255) not null, 
`modified_date` timestamp not null, `status` tinyint(1) not null, 
`created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

I’ve checked my completion that there is no error or any miss spelling
public function up()
    {
        Schema::create('dso', function (Blueprint $table) {
            $table->id();
            $table->foreignId('id_dso')->constrained('dso_table_name');
            $table->foreignId('id_rso')->constrained('rso_table_name');
            $table->foreignId('id_focus')->constrained('focus_table_name');
            $table->foreignId('id_wilayah')->constrained('wilayah_table_name');
            $table->foreignId('id_grup_wilayah')->constrained('grup_wilayah_table_name');
            $table->string('nama_dso');
            $table->string('created_by');
            $table->timestamp('created_date',$precision = 0);
            $table->string('modified_by');
            $table->timestamp('modified_date',$precision = 0);
            $table->boolean('status');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('dso');
    }
}

I’ve not added nullable because all column have to fill
I’m using Laravel 8.6 and on Windows with XAMPP and MariaDB

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The foreignId method creates an UNSIGNED BIGINT equivalent column, so there is no need to use unsignedBigInteger. Just replace it.

public function up()
{
    Schema::create('dso', function (Blueprint $table) {
        $table->id();
        $table->foreignId('id_dso');
        $table->foreignId('id_rso');
        $table->foreignId('id_focus');
        $table->foreignId('id_wilayah');
        $table->foreignId('id_grup_wilayah');
        $table->string('nama_dso');
        $table->string('created_by');
        $table->timestamp('created_date', $precision = 0);
        $table->string('modified_by');
        $table->timestamp('modified_date', $precision = 0);
        $table->boolean('status');
        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::dropIfExists('dso');
}

EDIT: If your table name does not match Laravel’s conventions, you may specify the table name by passing it as an argument to the constrained method:
public function up()
{
    Schema::create('dso', function (Blueprint $table) {
        $table->id();
        $table->foreignId('id_dso')->constrained('dso_table_name');
        $table->foreignId('id_rso')->constrained('rso_table_name');
        $table->foreignId('id_focus')->constrained('focus_table_name');
        $table->foreignId('id_wilayah')->constrained('wilayah_table_name');
        $table->foreignId('id_grup_wilayah')->constrained('grup_wilayah_table_name');
        $table->string('nama_dso');
        $table->string('created_by');
        $table->timestamp('created_date', $precision = 0);
        $table->string('modified_by');
        $table->timestamp('modified_date', $precision = 0);
        $table->boolean('status');
        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::dropIfExists('dso');
}

Method 2

Your issue is that you have used timestamps() instead of timestamp().

So, your migration should be like this:

public function up()
{
    Schema::create('dso', function (Blueprint $table) {
        $table->id();
        $table->foreignId('id_dso')->unique();
        $table->id('id_rso');
        $table->id('id_focus');
        $table->id('id_wilayah');
        $table->id('id_grup_wilayah');
        $table->string('nama_dso');
        $table->string('created_by');
        $table->timestamp('created_date');
        $table->string('modified_by');
        $table->timestamp('modified_date');
        $table->boolean('status');
        $table->timestamps();
    });
}

Check the available methods in the documentation.

Method 3

I believe that the problem is that you are declaring twice the same column.
$table->foreignId('id_dso'); creates an unsigned bit integer.
After that you use again $table->unsignedBigInteger('id_rso'); which does the same thing.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x