jeudi 22 novembre 2018

How to create laravel migration for tables with multiple foreign keys?

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?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire