Level 102
How are you checking if it has been added?
Jul 8, 2022
32
Level 5
Migration onDelete is not created on the table
I'm watching Jeffrey way's video:
https://laracasts.com/series/whats-new-in-laravel-9/episodes/2?page=1
Using Laravel 9.19, this code did not add onDelete cascade to the table:
use App\Models\User;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->foreignIdFor(User::class)->constrained()->onDelete('cascade');
$table->string('title');
$table->text('body');
$table->timestamps();
});
}
Why is that?
Level 5
@Sinnbeck phpMyAdmin - table structure - Relation View
Also, I deleted a user that has a post with no problem and the post stayed in posts table.
Level 102
@SigalZ are you sure the migration was run after the constraint was added?
Level 5
@Sinnbeck the constraint was there from the start. I thought maybe it creates the posts table before the users table and that's caused the problem so I rolledback this migration file only and ran it again, still no constraint added.
Level 102
@SigalZ are you able to run all migrations again? php artisan migrate:fresh
Level 39
Which db are you using?
Level 5
@sr57 MySQL
Level 39
works without pb with postgresql , could do the test later in mysql if nb did it before.
Level 5
@sr57 Thank you. Please let me know if you made it work with mysql
Level 2
@sr57 I reproduce the case with
Schema::create('testaxel', function(Blueprint $table) {
$table->foreignId('user_id')->onDelete('cascade');
});
No relation is created.
But works fine with :
Schema::create('testaxel', function(Blueprint $table) {
$table->foreignId('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});
This is the proper usage. foreignId does NOT make the foreign relation
1 like
Level 2
Confirmed: foreignId or foreignIdFor are NOT creating the relationship. It's just for formatting the field:
https://laravel.com/docs/9.x/migrations#column-method-foreignIdFor
Level 102
@axeloz but it's the second example here? https://laravel.com/docs/9.x/migrations#foreign-key-constraints
Level 2
@Sinnbeck sorry mate, you lost me here
Level 102
@axeloz oops. Wrong link from clipboard
Level 2
@Sinnbeck Oh I forgot about the constrained(). Ok I try again
Level 2
Working just fine with Laravel 9 and MySQL (mysql Ver 8.0.26-0ubuntu0.20.04.3 for Linux on x86_64)
Level 5
@axeloz Thank you.
From Laravel documentation:
"You may also specify the desired action for the "on delete" and "on update" properties of the constraint:
$table->foreignId('user_id') ->constrained() ->onUpdate('cascade') ->onDelete('cascade');"
I trie the same code, still no onDelete cascade created.
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->onDelete('cascade');
$table->string('title');
$table->text('body');
$table->timestamps();
});
I tried this as well:
public function up()
{
Schema::create('posts', function (Blueprint $table) {
$table->id();
//$table->foreignId('user_id')->constrained()->onDelete('cascade');
$table->foreignId('user_id');
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->string('title');
$table->text('body');
$table->timestamps();
});
}
Still no constraint added.
Level 39
same example as @sigalz with postgresl
\d tests
Table "public.tests"
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('tests_id_seq'::regclass)
user_id | bigint | | not null |
title | character varying(255) | | not null |
body | text | | not null |
created_at | timestamp(0) without time zone | | |
updated_at | timestamp(0) without time zone | | |
Indexes:
"tests_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"tests_user_id_foreign" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
the constraint is created with the delete cascade
Level 102
What version of mysql?
Level 5
@Sinnbeck 5.7.36
Level 39
Tested on MariaDB 10.3.34
Ok.
Level 39
Can you recreate your migration with a fresh new table ( test for example )?
Level 5
@sr57 I did. Same issue. I now also see this message on mysql workbench: foreign keys can only be defined for certain storage engines (like InnoDB).
I keep having to change the table to InnoDB after the migration.
Do you know of any settings that I can change to create the tables as InnoDB by default?
Maybe that's the problem?
Level 5
Looking at the table in mysql workbench, the foreign key was not created either, just an index called: posts_user_id_foreign but it does not display under the foreign keys tab, but under the indexes tab.
I tried to add the foreign key with the constraint manually but got this error:
Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE learn.posts
ADD CONSTRAINT users
FOREIGN KEY (user_id)
REFERENCES learn.users (id)
ON DELETE CASCADE
ON UPDATE NO ACTION;
ERROR 1215: Cannot add foreign key constraint
SQL Statement:
ALTER TABLE learn.posts
ADD CONSTRAINT users
FOREIGN KEY (user_id)
REFERENCES learn.users (id)
ON DELETE CASCADE
ON UPDATE NO ACTION
Level 39
Give the return of this command "show create table users;"
Level 5
@sr57 I'm sorry, where do I put this command?
Level 39
No more need if you don't use innodb.
Level 39
Which engine are you using?
For innodb (that should be the default)
https://stackoverflow.com/questions/11358849/mysql-innodb-engine-in-laravel
1 like
Level 5
@sr57 Thanks, changed that. Dropped the database. Now all works fine!!!!
InnoDB, that was the problem.
Thank you!
Level 39
Engines and foreign key constraint
https://www.w3resource.com/mysql/mysql-storage-engines.php
All engines, except InnoDB are NOT ACID compliant.
Please or to participate in this conversation.