samedi 30 décembre 2017

MySQL 1215 Cannot add foreign key constraint - Laravel 5

I'm stuck with this infamous error and can't figure out what went wrong. I'm trying to establish relationship between two tables orders and payments, whose migrations are defined as:

class CreateOrdersTable extends Migration
{
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();

            $table->integer('customer_id')->unsigned();
            $table->integer('partner_id')->unsigned();

            $table->string('status', 20)->default(Order::getDefaultStatus());
            $table->string('paid', 20)->default('no');

            $table->decimal('visitation_charges', 20, 2)->default(0); 
            $table->decimal('taxes', 20, 2)->default(0);
            $table->decimal('charges', 20, 2)->default(0);
            $table->decimal('discount', 20, 2)->default(0);
            $table->decimal('total', 20, 2)->default(0);

            $table->foreign('customer_id')->references('id')
                  ->on('customers')->onDelete('cascade')
                  ->onUpdate('cascade');
            $table->foreign('partner_id')->references('id')
                  ->on('partners')->onDelete('cascade')
                  ->onUpdate('cascade');
        });
    }

    public function down()
    {
        Schema::dropIfExists('orders');
    }
}

class CreatePaymentsTable extends Migration
{
    public function up()
    {
        Schema::create('payments', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();

            $table->integer('order_id')->unsigned();
            $table->string('gateway', 100);
            $table->string('transaction_id', 100);
            $table->decimal('amount', 20, 2);
            $table->string('status', 20)->default(Payment::getDefaultStatus());
            $table->string('comments', 2000)->nullable();

            $table->foreign('order_id')->references('id')
                  ->on('orders')->onDelete('set null')
                  ->onUpdate('cascade');
        });
    }

    public function down()
    {
        Schema::dropIfExists('payments');
    }
}

The error I get is:

[Illuminate\Database\QueryException]                                                                   
  SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: alter table `payments` add constraint `payments_order_id_foreign` foreign key (`order_id`) references `orders` (`id`) on delete set null on update cascade)

I've also verified that the table engines, column types, character set, etc., are the same (following are the outputs of show create):

| orders | CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `partner_id` int(10) unsigned NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'created',
  `paid` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'no',
  `visitation_charges` decimal(20,2) NOT NULL DEFAULT '0.00',
  `taxes` decimal(20,2) NOT NULL DEFAULT '0.00',
  `charges` decimal(20,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(20,2) NOT NULL DEFAULT '0.00',
  `total` decimal(20,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `orders_customer_id_foreign` (`customer_id`),
  KEY `orders_partner_id_foreign` (`partner_id`),
  CONSTRAINT `orders_customer_id_foreign` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_partner_id_foreign` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

| payments | CREATE TABLE `payments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `order_id` int(10) unsigned NOT NULL,
  `gateway` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transaction_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(20,2) NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `comments` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Not sure where I'm going wrong. :/



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire