1

I am creating a many to many relationship in Laravel 7 but get errors when trying to create my pivot table - Error is

SQLSTATE[HY000]: General error: 3780 Referencing column 'user_id' and referenced column 'id' 
in foreign key constraint 'groups_users_table_user_id_foreign' are incompatible.
(SQL: alter table `groups_users_table` add constraint `groups_users_table_user_id_foreign` 
foreign key (`user_id`) references `users` (`id`) on delete cascade)

My pivot table migration -

Schema::create('groups_users_table', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('users_id');
            $table->unsignedBigInteger('groups_id');
            $table->foreign('users_id')->references('id')->on('users')
                ->onDelete('cascade');
            $table->foreign('groups_id')->references('id')->on('groups')
                ->onDelete('cascade');
            $table->timestamps();
        });

Groups table -

public function up()
    {
        Schema::create('groups', function (Blueprint $table) {
            $table->increments('id');
            $table->text('name', 100);
            $table->text('description', 144);
            $table->timestamps();
        });
    }

            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

show create table users

results in

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

What is causing this issue?

Gamopo
  • 1,600
  • 1
  • 14
  • 22
peco60052
  • 29
  • 6
  • Laravel 7 uses bigInteger for increment columns. Try changing the columns in groups_users to bigInteger instead of integerr – aynber Jul 03 '20 at 12:22
  • Does this answer your question? [Laravel migration (errno: 150 "Foreign key constraint is incorrectly formed")](https://stackoverflow.com/questions/47728909/laravel-migration-errno-150-foreign-key-constraint-is-incorrectly-formed) – jewishmoses Jul 03 '20 at 12:24
  • Hi @aynber - I changed the groups_users table to be - `$table->bigInteger('users_id')->unsigned();` and it still gives the same error – peco60052 Jul 03 '20 at 12:25
  • Can you show the migration for your users table? That's what the migration is incompatible with. Also, if possible, do `show create table users` in your database and show that, just to double-check the current table. – aynber Jul 03 '20 at 12:33
  • @aynber I modified the post to include users table and show create table users – peco60052 Jul 03 '20 at 12:35
  • Are you creating Users and Groups table before referencing them in pivot table creation? – Nightwhistle Jul 03 '20 at 15:04

1 Answers1

0

I have fixed it, by removing $table->bigIncrements('id'); - I guess if I wanted an id for this field concatenating id from each table is how it should be done.

peco60052
  • 29
  • 6