Laravel Schema onDelete set null

Question

Can't figure out how to set proper onDelete constraint on a table in Laravel. (I'm working with SqLite)

$table->...->onDelete('cascade'); // works
$table->...->onDelete('null || set null'); // neither of them work

I have 3 migrations, creating the gallery table:

Schema::create('galleries', function($table)
{
    $table->increments('id');
    $table->string('name')->unique();
    $table->text('path')->unique();
    $table->text('description')->nullable();
    $table->timestamps();
    $table->engine = 'InnoDB';
});

Creating the pictures table:

Schema::create('pictures', function($table)
{
    $table->increments('id');
    $table->text('path');
    $table->string('title')->nullable();
    $table->text('description')->nullable();
    $table->integer('gallery_id')->unsigned();
    $table->foreign('gallery_id')
        ->references('id')->on('galleries')
        ->onDelete('cascade');
    $table->timestamps();
    $table->engine = 'InnoDB';
});

Linking gallery table to a picture:

Schema::table('galleries', function($table)
{
    // id of a picture that is used as cover for a gallery
    $table->integer('picture_id')->after('description')
        ->unsigned()->nullable();
    $table->foreign('picture_id')
        ->references('id')->on('pictures')
        ->onDelete('cascade || set null || null'); // neither of them works
});

I do not receive any errors. Also, even the "cascade" option doesn't work (only on the gallery table). Deleting a gallery deletes all pictures. But deleting the cover picture, wont delete the gallery (for test purposes).

Since even the "cascade" is not triggered, I "set null" is not the problem.

EDIT (workaround):

After reading this article I've changed my schema a bit. Now, the pictures table contains an "is_cover" cell, that indicates whether this picture is a cover on its album or not.

A solution to the original problem is still highly appreciated!

score 425 · Accepted Answer · answered Sep 10 '14 at 12:46

425

If you want to set null on delete:

$table->...->onDelete('set null');

First make sure you set the foreign key field as nullable:

$table->integer('foreign_id')->unsigned()->nullable();

answered Sep 10 '14 at 12:46

Johan

4,689
1
13
9

50

plus for `->nullable()` – mohsenJsh Feb 16 '16 at 11:59
3

Is there some Laravel documentation o this? – Chuck Le Butt Feb 18 '19 at 19:44
https://laravel.com/docs/6.x/migrations#foreign-key-constraints it doesn't document what options there are, but I think you can assume it's the default mysql values (see `../ vendor / laravel / framework / src / Illuminate / Database / Schema / Grammars / Grammar.php`) – Dirk Jan Mar 23 '20 at 14:47

score 41 · Answer 2 · edited Feb 15 '23 at 15:19

41

In laravel 7 and 8 you can use:

$table->foreignId('foreign_id')->nullable()->constrained("table_name")->cascadeOnUpdate()->nullOnDelete();

Reference

The different options are declared in the class Illuminate\Database\Schema\ForeignKeyDefinition (see source).

edited Feb 15 '23 at 15:19

T.Palludan

176
1
2
13

answered Dec 14 '20 at 05:20

Ayman Elshehawy

2,746
23
21

Tested with Laravel 7, also works there. – gabelbart Nov 22 '21 at 11:49
Order matters! In case anyone (like me) places 'nullable()' after 'constrained()' you'll get the following error: "General error: 1830 Column 'foreign_id' cannot be NOT NULL..." Just make sure that nullable is called before constrained! – T.Palludan Feb 14 '23 at 10:13

score 6 · Answer 3 · answered Jan 01 '14 at 13:32

6

According to

http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

$table->onDelete('set null') should work prehaps try

$table->...->onDelete(DB::raw('set null'));

If there are any errors, would also be helpful

answered Jan 01 '14 at 13:32

Chris Barrett

516
3
12

You might want to roll back, write out the sql by hand and then execture and run tests on local. Everything I can find says that should work http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html, could be an issue generating the sql – Chris Barrett Jan 01 '14 at 13:55
Thanks! Unfortunately it resulted in the same issue. I think it's something specific to SqLite and circular references. – M K Jan 01 '14 at 14:17
Plus one because onDelete('set null') works with mysql. – Simon Bengtsson Jul 01 '14 at 11:12

score 6 · Answer 4 · edited May 23 '17 at 12:18

6

~~This is a known issue in Laravel. More info about this here.~~
This feature is not supported in SQLite, see here
Also a topic that has a detailed showdown of this problem

edited May 23 '17 at 12:18

Community

1
1

answered Jan 02 '14 at 00:40

M K

9,138
7
43
44

1

@SimonBengtsson The issue must have been closed or deleted. – M K Aug 28 '14 at 09:58

score 4 · Answer 5 · answered Mar 19 '21 at 13:28

4

in laravel 8 you can do it like this.

 $table->foreignId('table_id')->nullable()->constrained()->onDelete('set null');

nullable() column modifiers must be called before constrained() and onDelete('set null')

answered Mar 19 '21 at 13:28

Ali Akbar Afridi

676
7
7

score 3 · Answer 6 · answered Aug 26 '14 at 10:25

3

Using Laravel 4.2 on MySQL 5.5 with InnoDB, onDelete('set null') works.

answered Aug 26 '14 at 10:25

Mark Kendall

129
2
10

score 1 · Answer 7 · answered Jan 07 '22 at 06:53

1

you can use nullOnDelete() method as well. See laravel 8.x https://laravel.com/docs/8.x/migrations#foreign-key-constraints

$table->foreignId('table_id')->nullable()->constrained()->nullOnDelete();

answered Jan 07 '22 at 06:53

Neeraj Tangariya

1,159
1
17
29

score 0 · Answer 8 · answered Apr 12 '21 at 14:22

0

SQLite doesn't support the ADD CONSTRAINT variant of the ALTER TABLE command

SQLite foreign key dont set null on delete in phpunit test

answered Apr 12 '21 at 14:22

AmirRezaM75

1,030
14
17

Laravel Schema onDelete set null

8 Answers8

In laravel 7 and 8 you can use:

Linked