Migration: Column already exists error when creating new tables

Question

I did a search on possible resolutions to this issue and I couldn't find anything when creating a new table in Laravel 7. But, I am getting a Column already exists error when running php artisan migrate while creating new tables. The column that is in the error is contract_type_id.

Here is a snip of the migration file:

public function up()
{
    Schema::create('contract_data', function (Blueprint $table) {
        $table->id();
        $table->integer('contract_type_id');
        $table->integer('customer_id');
        $table->date('start_date');
        $table->date('end_date');
        $table->integer('product1_id');
        $table->integer('product2_id');
        $table->integer('product3_id');
        $table->integer('product4_id');
        $table->integer('product5_id');
        $table->integer('product1_gallons');
        $table->integer('product2_gallons');
        $table->integer('product3_gallons');
        $table->integer('product4_gallons');
        $table->integer('product5_gallons');
        $table->decimal('product1_price', 9,6 );
        $table->decimal('product2_price', 9,6 );
        $table->decimal('product3_price', 9,6 );
        $table->decimal('product4_price', 9,6 );
        $table->decimal('product5_price', 9,6 );
        $table->integer('fuel_contract_number');
        $table->timestamps();
    });

    Schema::table('contract_data', function(Blueprint $table) {
        $table->foreignId('contract_type_id')->constrained('contract_type');
        $table->foreignId('customer_id')->constrained('customer_details');
        $table->foreignId('product1_id')->constrained('product_details');
        $table->foreignId('product2_id')->constrained('product_details');
        $table->foreignId('product3_id')->constrained('product_details');
        $table->foreignId('product4_id')->constrained('product_details');
        $table->foreignId('product5_id')->constrained('product_details');
        $table->foreignId('fuel_contract_number')->constrained('fuel_contracts');
    });
}

Error aside, take another look at your data structure... Anytime you have multiple of the same columns, with only a difference in the number is an opportunity to correctly normalize your data. You should have a pivot table for products instead of product{x}_ columns. — Tim Lewis
– Tim Lewis, Commented Jul 23, 2020 at 16:29
@TimLewis thank you for the feedback. where would i find documentation on working with pivot tables? — Geoffrey
– Geoffrey, Commented Jul 23, 2020 at 16:30
It's a pretty complicated subject, but you'll need to do some research into Database Normalization (Normal Forms, 1st Normal, 3rd Normal, etc etc). en.wikipedia.org/wiki/Database_normalization summarizes it, but there's a lot more to it. — Tim Lewis
– Tim Lewis, Commented Jul 23, 2020 at 16:32
As for the error, I'm pretty sure one of the functions foreignId or constrained also creates the column, but you're already creating it in the first block of the migration. Read over the documentation for them here: laravel.com/docs/7.x/migrations#foreign-key-constraints — Tim Lewis
– Tim Lewis, Commented Jul 23, 2020 at 16:33
Yeah, it's an either or; you either define the column and set the index in two separate lines: $table->unsignedBigInteger('user_id'); and $table->foreign('user_id')->references('id')->on('users');, or you do it in a single line: $table->foreignId('user_id')->constrained();. In your code, you have both, which is why you're getting that error. — Tim Lewis
– Tim Lewis, Commented Jul 23, 2020 at 16:36

STA · Accepted Answer · 2020-07-23 18:06:46Z

3

The foreignId method is an alias for unsignedBigInteger while the constrained method will use convention to determine the table and column name being referenced
So you are making contract_type_id field twice.

$table->foreignId('contract_type_id')->constrained('contract_type'); alias of $table->unsignedBigInteger('contract_type_id'); & $table->foreign('contract_type_id')->references('id')->on('contract_type);

So in your second migration it would be :

$table->foreign('contract_type_id')->references('id')->on('contract_type);

Instead of :

$table->foreignId('contract_type_id')->constrained('contract_type');

Also you need to use Unsigned Big Integer, as :

$table->unsignedBigInteger('contract_type_id');

Not,

$table->integer('contract_type_id');

Because Laravel 5.8 Added unsignedBigInteger as defaults for foreign key, also for Laravel 6 and 7

edited Jul 23, 2020 at 18:06

answered Jul 23, 2020 at 16:40

STA

35.3k9 gold badges49 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geoffrey Over a year ago

I ended up creating a model, adjusting the Schema like listed above, and then ran a migration. I did this for each table i was creating and i was able to get all the tables made. It felt like a clunky process, but i'm sure i was doing it wrong. Either way all the tables are created.

Collectives™ on Stack Overflow

Migration: Column already exists error when creating new tables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related