Level 122
From your description, I assume the column is ->nullable()?
The only thing I can suggest is to check the config in tinker `config('database') to ensure this setting is in place.
Apr 14, 2020
11
Level 17
SQLite foreign key dont set null on delete in phpunit test
Hello,
I have migration where I add foreign key to threads table (https://github.com/keizah7/forum/commit/e1aa1b31555c9469091be178e23f5746c377c080#diff-873a697d9df8d6d454c40acbb165080e)
Problem is that in phpunit test sqlite don't set foreign key null on delete.
1) Tests\Feature\BestReplyTest::if_a_best_reply_is_deleted_then_the_thread_is_properly_updated_to_reflect_that
Failed asserting that '1' is null.
For temporary solution I do it manually - I am using model static::deleted and then test is passing.
Where is the problem?
Jeffrey covered this topic in https://laracasts.com/series/lets-build-a-forum-with-laravel/episodes/82
but his solution doesn't help me. I'm using Laravel 7.3 and foreign_key_constraints is true by default.
Level 17
Yes, best reply id is nullable. https://github.com/keizah7/forum/blob/e1aa1b31555c9469091be178e23f5746c377c080/database/migrations/2020_03_31_125699_create_threads_table.php
I checked database config and it is"foreign_key_constraints" => true as I said before
Level 43
@keizah I think that you should try to call this code on the static::deleting and not on static::deleted:
static::deleting(function ($reply) {
if ($reply->isBest()) {
$reply->thread->update(['best_reply_id' => null]);
}
// ...
});
Level 17
This code is unnecessary. In local enviroment with MySql database deleted reply sets null to thread best_reply_id without this line. I added this line just to pass phpunit test, because deleted best reply in sqlite don't set null automatically. This is the problem, and I need a solution
static::deleted(function ($reply) {
if ($reply->isBest()) {
$reply->thread->update(['best_reply_id' => null]);
}
}
Level 73
I'm a bit allergic to allowing nullable on foreign keys. I suggest you move the best reply to the replies table instead.
However...
Have you enabled foreign key constraints in your migration? It's not enabled by default
Schema::enableForeignKeyConstraints();
Schema::disableForeignKeyConstraints();
Level 17
Why you don't like it? It is enabled by default, I checked, even when I try to enable it I got same result
Level 73
The main reason is that if you put a foreign key constraint on a table, let's say the users table.
- id
- username
- password
- profile_id
That means that every user must have a profile and that is all good.
However in your case, a question does not always have a best reply.
Then it's not optimal to put it on the query
- id
- query
- user_id
- best_reply_id
so instead you move the best reply to the replies table instead
- id
- query_id
- reply
- user_id
- best_reply
Or even move it to a pivot table best_replies or some such
- question_id
- reply_id
That makes more sense at least to me than making it nullable.
Level 17
Thanks for advice, I just followed Jeffrey tutorial, so this was his decision but it is not working
Level 1
I have the same issue on his tutorial (laravel 8), did you figure it out?
Level 17
No. I deleted record manually
Level 1
I figure it out why it's working for jeffery not us.
because we are adding best_reply_id column after creating threads table (we are altering table in other migration file like add_answer_id_to_threads_table) and SQLite doesn't support the ADD CONSTRAINT variant of the ALTER TABLE command
SQL Features That SQLite Does Not Implement.
Schema::table('threads', function (Blueprint $table) {
$table->foreignId('answer_id')
->nullable()
->constrained('replies', 'id')
->nullOnDelete()
->after('category_id');
});
It works fine in MySQL
or we can code above in create_threads_table
but we will get foreign key constraint because replies table will be created after threads table
we can change reply migration's file name timestamp to earlier date. but we must use simple unsignedInteger column without referencing.
Schema::create('replies', function (Blueprint $table) {
/*$table->foreignIdFor(User::class)->constrained()->onDelete('cascade');
$table->foreignIdFor(Thread::class)->constrained()->onDelete('cascade');*/
$table->unsignedInteger('thread_id');
$table->unsignedInteger('user_id');
});
but i prefer to ignore test rather this
1 like
Please or to participate in this conversation.