General error: 1364 Field 'manufacturer_name' doesn't have a default value

Question

I get manufacturer name has no value even tho it references id column in manufacturer table which is already created in database and sits in the same row with manufacturer name. Why is so? Here is code:

Manufacturer table

Schema::create('manufacturer', function (Blueprint $table) {
    $table->bigIncrements('id')->autoIncrement();
    $table->string('manufacturer_name');
    $table->string('name');
    $table->timestamps();
});

Planes table

Schema::create('planes', function (Blueprint $table) {
        $table->bigIncrements('id')->autoIncrement();
        $table->string('icao24');
        $table->bigInteger('manufacturer_name')->unsigned()->default(1);
        $table->foreign('manufacturer_name')->references('id')->on('manufacturer');
        $table->timestamps();
    });

Inserting values:

Manufacturer table:

$file = fopen('https://opensky-network.org/datasets/metadata/aircraftDatabase.csv', 'r');

        $headersArray = [];
        $i = 0;

        $headers = fgetcsv($file);

        foreach ($headers as $key => $value) {
            array_push($headersArray, $value);
        }

        while ($i < 50) {
            $line = fgetcsv($file);

            $comb = array_combine($headersArray, $line);

            DB::table('manufacturer')->insert(array(
                'name' => $comb['manufacturername'],
                'created_at' => date('Y-m-d H:m:s'),
                'updated_at' => date('Y-m-d H:m:s')
            )); .....

Planes table:

Same loop just different schema:

DB::table('planes')->insert(array(
                'icao24' => $comb['icao24'],
                'created_at' => date('Y-m-d H:m:s'),
                'updated_at' => date('Y-m-d H:m:s')
            ));

I expect to have incremental ids in plane table inside manufacturer_name column that points to manufacturer name in Manufacturer table. Now it's just all default value 1 in all rows.

Here is how Manufacturer DB looks now:

enter image description here

I want these ids 2, 3, 4... be inside Planes table as foreign keys in manufacturer name column, but instead now it looks like this:

enter image description here

Appreciate the help.

Answer 1

See if this helps.

$file = fopen('https://opensky-network.org/datasets/metadata/aircraftDatabase.csv', 'r');

$headersArray = [];
$i = 0;

$headers = fgetcsv($file);

foreach ($headers as $key => $value) {
   array_push($headersArray, $value);
}

while ($i < 50) {
    $line = fgetcsv($file);

    $comb = array_combine($headersArray, $line);

    // Look up manuf to see if we already inserted it.
    // Hopefully name isn't duplicated
    $manuf = DB::table('manufacturer')->where('name', $comb['manufacturername'])->first();
    // Only insert if it isn't already there
    if ( ! $manuf ) {
        $manufId = DB::table('manufacturer')->insertGetId(array(
            'name' => $comb['manufacturername'],
        ));
    } else {
        $manufId = $manuf->id;
    }

    DB::table('planes')->insert(array(
        'icao24' => $comb['icao24'],
        'manufacturer_name' => $manufId
    ));

}

Migrations and foreign keys

Schema::create('planes', function (Blueprint $table) {
    $table->bigIncrements('id')->autoIncrement();
    $table->string('icao24');
    $table->bigInteger('manufacturer_name')->unsigned()->default(1); // (1)
    $table->foreign('manufacturer_name')->references('id')->on('manufacturer'); // (2)
    $table->timestamps();
});

Migrations only describe how to build tables in the database. They don't cause data to be inserted into tables. By defining (1) and (2) above you are defining a column and describing how that column relates to another table.

When inserting data into the tables, your code must insert the correct foreign key values. The migration or the database is not going to do that for you.