Htykuut

I want to create a table with multiple foreign keys. Here is the sql:

CREATE TABLE `customers` (

    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `name` varchar(50) DEFAULT NULL,

    `address` varchar(100) DEFAULT NULL,

    `email` varchar(50) DEFAULT NULL,

    `type_id` tinyint(3) unsigned DEFAULT NULL,

    `district_id` tinyint(3) unsigned DEFAULT NULL,

    `city_id` tinyint(3) unsigned DEFAULT NULL,

    `business_id` tinyint(3) unsigned DEFAULT NULL,

    `group_id` tinyint(3) unsigned DEFAULT NULL,

    PRIMARY KEY (`id`),

    KEY `FK_customer_1` (`district_id`,`city_id`),

    KEY `FK_customer_2` (`business_id`),

    KEY `FK_customer_3` (`group_id`),

    KEY `FK_customer_4` (`type_id`),

    CONSTRAINT `FK_customer_1` FOREIGN KEY (`district_id`, `city_id`)

    REFERENCES `cities` (`district_id`, `city_id`) ON DELETE CASCADE,

    CONSTRAINT `FK_customer_2` FOREIGN KEY (`business_id`) REFERENCES `businesses` (`business_id`) ON DELETE CASCADE,

    CONSTRAINT `FK_customer_3` FOREIGN KEY (`group_id`) REFERENCES `groups` (`group_id`) ON DELETE CASCADE,

    CONSTRAINT `FK_customer_4` FOREIGN KEY (`type_id`) REFERENCES `types` (`type_id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I wrote a migration file with the following:

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

    $table->increments('id');

    $table->string('name',50);

    $table->string('address',100)->nullable();

    $table->string('email',50)->nullable();

    $table->integer('type_id')->unsigned()->index();

    $table->integer('district_id')->unsigned()->index();

    $table->integer('city_id')->unsigned()->index();

    $table->integer('business_id')->unsigned()->index();

    $table->integer('group_id')->unsigned()->index();

    $table->timestamps();



    $table->foreign(['district_id','city_id'])->references(['district_id','city_id'])->on('cities')->onDelete('cascade');

    $table->foreign('business_id')->references('business_id')->on('businesses')->onDelete('cascade');

    $table->foreign('type_id')->references('type_id')->on('types')->onDelete('cascade');

    $table->foreign('group_id')->references('group_id')->on('groups')->onDelete('cascade');

});

But when I run the migration it gives me the following error.

SQLSTATE[HY000]: General error: 1005 Can't create table `wastewater`.`#sql-5a8_18a` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `customers` add constraint `customers_district_id_city_id_foreign` foreign key (`district_id`, `city_id`) references `cities` (`district_id`, `city_id`) on delete cascade)`

How can I this?

Can you please Try this way, Just removed array and define with comma-separated only.

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

    $table->increments('id');

    $table->string('name',50);

    $table->string('address',100)->nullable();

    $table->string('email',50)->nullable();

    $table->integer('type_id')->unsigned()->index();

    $table->integer('district_id')->unsigned()->index();

    $table->integer('city_id')->unsigned()->index();

    $table->integer('business_id')->unsigned()->index();

    $table->integer('group_id')->unsigned()->index();

    $table->timestamps();



    $table->foreign('district_id','city_id')->references('district_id','city_id')->on('cities','cities')->onDelete('cascade','cascade');

    $table->foreign('business_id')->references('business_id')->on('businesses')->onDelete('cascade');

    $table->foreign('type_id')->references('type_id')->on('types')->onDelete('cascade');

    $table->foreign('group_id')->references('group_id')->on('groups')->onDelete('cascade');

});

Try the following way, it will work.

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

    $table->increments('id');



    $table->integer('type_id', false, true);

    $table->foreign('type_id')->references('type_id')->on('types')

        ->onDelete('cascade');



    $table->integer('district_id', false, true);

    $table->foreign('district_id')

        ->references('district_id')->on('cities')

        ->onDelete('cascade');



    $table->integer('city_id', false, true);

    $table->foreign('city_id')

        ->references('city_id')->on('cities')

        ->onDelete('cascade');



    $table->integer('business_id', false, true);

    $table->foreign('business_id')->references('business_id')

        ->on('businesses')->onDelete('cascade');



    $table->integer('group_id', false, true);

    $table->foreign('group_id')->references('group_id')->on('groups')

        ->onDelete('cascade');

            $table->string('name',50);

    $table->string('address',100)->nullable();

    $table->string('email',50)->nullable();

    $table->timestamps();

});