EF Core sets the Foreign Key constraint as 'ON DELETE RESTRICT' even though the FK is nullable

[v8ify]

File a bug

I am using EF Core with PostgreSQL using Npgsql and I have set the Foreign Key of the dependent entity (Chapter, see below) as nullable int. I was expecting when I delete the principle entity (in this case Book) CASCADE NULL will happen. Instead in the migration the behavior is ON DELETE RESTRICT. So when I try to delete the Book entity it throws

update or delete on table "Books" violates foreign key constraint "FK_Chapters_Books_BookId" on table "Chapters"

Shouldn't it be ON DELETE SET NULL by default? How to make it SET NULL?

Include your code

Book entity:

[Index(nameof(ISBN))]
public class Book
{
    public int BookId { get; set; }

    public string ISBN { get; set; }

    public List<Chapter> Chapters { get; set; }
}

Chapter entity:

public class Chapter
{
    public int ChapterId { get; set; }

    public string Title { get; set; }

    public int? BookId { get; set; }    //  <--- nullable
    public Book Book { get; set; }
}

Generated Migration for Chapter Entity:

migrationBuilder.CreateTable(
    name: "Chapters",
    columns: table => new
    {
        ChapterId = table.Column<int>(type: "integer", nullable: false)
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
        Title = table.Column<string>(type: "text", nullable: true),
        BookId = table.Column<int>(type: "integer", nullable: true),
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Chapters", x => x.ChapterId);
        table.ForeignKey(
            name: "FK_Chapters_Books_BookId",
            column: x => x.BookId,
            principalTable: "Books",
            principalColumn: "BookId",
            onDelete: ReferentialAction.Restrict);         // <---  Why??????
    });

As you can see above the onDelete: ReferentialAction.Restrict) has restrict in it. Why not SetNull by default?

Include stack traces

Message: 
    Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
    ---- Npgsql.PostgresException : 23503: update or delete on table "Books" violates foreign key constraint "FK_Chapters_Books_BookId" on table "Chapters"
  Stack Trace: 
    ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
    BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
    RelationalDatabase.SaveChanges(IList`1 entries)
    StateManager.SaveChanges(IList`1 entriesToSave)
    StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
    NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
    StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
    DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
    DbContext.SaveChanges()
    BookServicesTests.RegisterNewBook_CanSuccessfullySaveToDatabase() line 63
    --- End of stack trace from previous location ---
    ----- Inner Stack Trace -----
    NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
    NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
    NpgsqlDataReader.NextResult()
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
    NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    DbCommand.ExecuteReader()
    RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
    ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Include provider and version information

EF Core version: 5.0.4
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.10)

[AndriySvyryd]

This is mostly for backward-compatibility, see #21252 (comment)