Level 75
It's a foreign key so
https://laravel.com/docs/8.x/migrations#foreign-key-constraints
Jun 7, 2021
9
Level 2
Index() not applied on migration
Hi, I've the next line on a migration method on upload or create table method:
$table->integerIncrements('storage_id')->index()->comment("FK(storages.id)");
But the column is created without index, and don't know whi What's the problem?
Level 2
It doesn't work as foreign key, that this is what I want. This is my current code:
Schema::create('contents_video', function (Blueprint $table) {
$table->integerIncrements('id');
$table->integer('content_id')->index()->comment("FK(contents.id)");
$table->string("video_format", 15)->nullable()->comment("Optional, the video format encoded Divx, xvid, mp4, etc...");
$table->string("language", 2)->nullable()->comment("Optional, main language in iso 2-characters, i.e. 'es', 'en'");
$table->string("audio_format", 15)->nullable()->comment("Optional, the audio format encoded, mp3, AC3, etc...");
$table->string("audio_channels", 10)->nullable()->comment("Optional, a text description about audio tracks for multilanguage, i.e. 'es,en,ca'");
$table->tinyInteger("subtitles")->default(0)->comment("0: No subtitles or N/A, 1: Integrated subtitles, 2: If video contains subtitles as an srt file or something else");
$table->string("dual_info", 100)->nullable()->comment("Optional, When video contains multi audio languages, can be set here");
$table->smallInteger("duration")->nullable()->comment("Optional, to put here the duration of the video in minuts");
$table->bigInteger('created_at');
$table->bigInteger('updated_at');
// $table->timestamps();
$table->foreign('content_id')->references('id')->on('contents')->onDelete('set null')->onUpdate('cascade');
});
I get this error when I exceute the migration entirely:
artisan migrate:refresh
...
Migrated: 2021_06_03_175449_create_contents_table (41.16ms)
Migrating: 2021_06_04_153907_create_contents_video_table
Illuminate\Database\QueryException
SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'contents_video' already exists (SQL: create table `contents_video` (`id` int unsigned not null auto_increme
nt primary key, `content_id` int not null comment 'FK(contents.id)', `video_format` varchar(15) null comment 'Optional, the video format encoded Divx, xvid, mp4, etc...', `l
anguage` varchar(2) null comment 'Optional, main language in iso 2-characters, i.e. \'es\', \'en\'', `audio_format` varchar(15) null comment 'Optional, the audio format enco
ded, mp3, AC3, etc...', `audio_channels` varchar(10) null comment 'Optional, a text description about audio tracks for multilanguage, i.e. \'es,en,ca\'', `subtitles` tinyint
not null default '0' comment '0: No subtitles or N/A, 1: Integrated subtitles, 2: If video contains subtitles as an srt file or something else', `dual_info` varchar(100) nu
ll comment 'Optional, When video contains multi audio languages, can be set here', `duration` smallint null comment 'Optional, to put here the duration of the video in minut
s', `created_at` bigint not null, `updated_at` bigint not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')
When I remove the index() call, it success the same error
Level 75
Use
php artisan migrate:fresh
https://laravel.com/docs/8.x/migrations#drop-all-tables-migrate
And remove this line
$table->integer('content_id')->index()->comment("FK(contents.id)");
Level 2
I removed the line:
//$table->integer('content_id')->index()->comment("FK(contents.id)");
And applied migrate:fresh :
Migrated: 2021_06_03_175449_create_contents_table (52.78ms)
Migrating: 2021_06_04_153907_create_contents_video_table
Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'content_id' doesn't exist in table (SQL: alter table `contents_video` add constraint `contents_video_co
ntent_id_foreign` foreign key (`content_id`) references `contents` (`id`) on delete set null on update cascade)
Level 75
Read the documentation properly.
$table->foreignId('content_id')
->constrained()
->onUpdate('cascade')
->onDelete('set null');
Level 2
No working:
Schema::create('contents_video', function (Blueprint $table) {
$table->integerIncrements('id');
//$table->integer('content_id')->index()->comment("FK(contents.id)");
$table->string("video_format", 15)->nullable()->comment("Optional, the video format encoded Divx, xvid, mp4, etc...");
$table->string("language", 2)->nullable()->comment("Optional, main language in iso 2-characters, i.e. 'es', 'en'");
$table->string("audio_format", 15)->nullable()->comment("Optional, the audio format encoded, mp3, AC3, etc...");
$table->string("audio_channels", 10)->nullable()->comment("Optional, a text description about audio tracks for multilanguage, i.e. 'es,en,ca'");
$table->tinyInteger("subtitles")->default(0)->comment("0: No subtitles or N/A, 1: Integrated subtitles, 2: If video contains subtitles as an srt file or something else");
$table->string("dual_info", 100)->nullable()->comment("Optional, When video contains multi audio languages, can be set here");
$table->smallInteger("duration")->nullable()->comment("Optional, to put here the duration of the video in minuts");
$table->bigInteger('created_at');
$table->bigInteger('updated_at');
// $table->timestamps();
$table->foreignId('content_id')->constrained()->onDelete('set null')->onUpdate('cascade');
});
And the result after migrate:fresh:
Migrated: 2021_06_03_175449_create_contents_table (47.16ms)
Migrating: 2021_06_04_153907_create_contents_video_table
Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 1005 Can't create table `sorg`.`contents_video` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `contents_vid
eo` add constraint `contents_video_content_id_foreign` foreign key (`content_id`) references `contents` (`id`) on delete set null on update cascade)
at D:\projects\smartOrganizer\vendor\laravel\framework\src\Illuminate\Database\Connection.php:678
674▕ // If an exception occurs when attempting to run a query, we'll format the error
Level 75
As id always use
$table->id();
https://laravel.com/docs/8.x/migrations#column-method-id
This is the last time I answer you.
Level 2
I can't use id() due is creates as big integer, and the parent table content is created as intger ai primary key:
Schema::create('contents', function (Blueprint $table) {
$table->integerIncrements('id');
....
});
So is not able to use another syntax. The question is why laravel doesn't creates the column id_content as index is I want, when I create with phpmyadmin the col content_id in contents_video table, then the creation of foreign key works fine.
Level 2
Solved by myself: The problem was here:
$table->unsignedInteger('content_id')->index()->unique()->comment("FK(contents.id)");
As you can see the new field can't be null, while I was trying to create constraint as:
$table->foreign('content_id')->references('id')->on('contents')->onDelete('set null')->onUpdate('cascade');
On delete set null is impossible, so the solution can be both:
$table->unsignedInteger('content_id')->index()->nullable()->unique()->comment("FK(contents.id)");
...
$table->foreign('content_id')->references('id')->on('contents')->onDelete('set null')->onUpdate('cascade');
Or forcing the deletion of children associated data:
$table->unsignedInteger('content_id')->index()->unique()->comment("FK(contents.id)");
...
$table->foreign('content_id')->references('id')->on('contents')->onDelete('cascade')->onUpdate('cascade');
I've finally applied the second one, due I don't want to mantain children data when the parent is deleted.
Please or to participate in this conversation.