minaremonshaker's avatar

Issue with Foreign Key Creation in Laravel Migration

Hi, I’m encountering an issue with a migration that creates a tickets table, which has a one-to-many relationship with the users table. Normally, the tickets table should include a foreign key referencing the users table. However, when I try to define it using: $table->foreignId('user_id')->constrained(indexName: 'FK_USER_TICKETS')->onDelete('cascade'); it only creates the user_id column without actually generating the foreign key constraint in the database, even though I expected it to.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void
    {
        Schema::create('tickets', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained(indexName: 'FK_USER_TICKETS')->onDelete('cascade');
            $table->string('title' , 40);
            $table->text('description');
            $table->string('status');
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('tickets');
    }
};
0 likes
11 replies
Snapey's avatar

Your users table has an id column?

Why do you need to specify the key name?

minaremonshaker's avatar

hi , first of all Mary Christmas and hope you are doing well , please can you clarify more, i am adding a forging key to the tickets table cause there are a one to many relation but the problem i that the method above dose not create it in the database tickets table , also for the key name i like to make all of the keys start with specific prefix like FL, UQ , etc...

Tray2's avatar

If you are using MySQL/MariaDB you don't need to add it manually like that, it's added automatically.

I just created this migration.

 public function up(): void
    {
        Schema::create('books', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained()->onDelete('cascade');
            $table->timestamps();
        });
    }

And when I run show index from books

I get the following

If I add the index name like you did

 public function up(): void
    {
        Schema::create('books', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained(indexName: 'FK_BOOK_USER')->onDelete('cascade');
            $table->timestamps();
        });
    }

Then I get

  {
    "Table": "books",
    "Non_unique": 1,
    "Key_name": "FK_BOOK_USER",
    "Seq_in_index": 1,
    "Column_name": "user_id",
    "Collation": "A",
    "Cardinality": 0,
    "Sub_part": null,
    "Packed": null,
    "Null": "",
    "Index_type": "BTREE",
    "Comment": "",
    "Index_comment": "",
    "Visible": "YES",
    "Expression": null
  }
minaremonshaker's avatar

Hi , But in some cases i like to name the index , what I know that it is suppose to be created in both cases because the constrained method already has this parameter where I can can add the table name and the id and the index name

minaremonshaker's avatar

what make me surprised that the create got the foreign key but i cant see in in phpmyadmin digram

CREATE TABLE `tickets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `title` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_USER_TICKETS` (`user_id`),
  FULLTEXT KEY `tickets_title_fulltext` (`title`) /*!50100 WITH PARSER `ngram` */ ,
  FULLTEXT KEY `tickets_description_fulltext` (`description`) /*!50100 WITH PARSER `ngram` */ ,
  FULLTEXT KEY `tickets_title_description_fulltext` (`title`,`description`) /*!50100 WITH PARSER `ngram` */ ,
  CONSTRAINT `FK_USER_TICKETS` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Tray2's avatar

I would suggest using a better tool than phpmyadmin.

1 like

Please or to participate in this conversation.