CODEWITHSUNDEEP
mysqllaravel-5foreign-keys
ankush981
ankush981

Reputation: 5417

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. :/

Upvotes: 1

Views: 1381

Answers (3)

Tuhin Bepari
Tuhin Bepari

Reputation: 745

Add nullable() on order_id column. Like below. Your error will be gone. 

class CreatePaymentsTable extends Migration
{
    public function up()
    {
        Schema::create('payments', function (Blueprint $table) {
        // your others columns
         $table->integer('order_id')->unsigned()->nullable();
    }
}

Upvotes: 2

Milano
Milano

Reputation: 176

it should better you build another migration for foreign key:

php artisan make:migration add_foreign_key_to_payment_table --table=payments

so add payments foreign key to this migration, then run php artisan migrate

if the error still exists you must drop manually orders and payments table and remove them from migration table then add those again.

I hope this will help you. Good day! :)

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562280

You can't make a foreign key ON DELETE SET NULL for columns declared as NOT NULL.

The column can't be NULL.

The foreign key works if you do either of the following:

  • Omit the ON DELETE SET NULL:

    alter table `payments` add constraint `payments_order_id_foreign` 
foreign key (`order_id`) references `orders` (`id`) on update cascade;

  • Modify the column to allow NULL:

    alter table payments modify order_id int(10) unsigned null;

Upvotes: 4

Related Questions