Entity Framework Core 2 – Referential Actions

Let’s suppose that we have a one-to-many relationship between two tables, the table Students and the table Contacts, where each student can have a set of contacts.

When you have related records between different tables, there is a question that we should always ask ourselves, what will happen when a record is deleted? For example, in our one-to-many relationship, we have to give each student a set of contacts. What will happen to a student’s contacts if that student is removed from the database? To define this behavior we have referential actions.

Referential actions are the actions that the database will take when a record that is being referenced in another table is updated or eliminated. In our case, we are going to concentrate on the elimination of a record. In EF Core 2.1, we have the following options to answer the question: What will happen to a student’s contacts when the student is deleted from the database?

  1. Cascade: This is the option that EF Core uses by default. The result of this option is that the contacts will follow the same destination as the student. If the student is eliminated, their contacts will also be eliminated.
  2. No Action: Literally, this means doing nothing. Of course this can cause a data reference error in our case when you try to delete a student and you do not delete the contacts. The reason for this error is that the foreign key in the contacts table refers to a record in the students table, if that record in the students table does not exist, then an error is thrown, because we have a data integrity problem.
  3. Restrict: If the action to be taken results in a loss of data integrity, then the action will not be executed.
  4. Set Default: In this case, the value of the foreign key is modified to one by default. We have the option to indicate default values for our columns. This default value would be used in case the record which is being referenced is deleted.
  5. Set Null: In this case null will be set as the value of the referencing column.

As we can see, we have several options which we can use according to the action we want to perform on the contact records. Although, it is important to note that EF Core works with several database engines, and these engines are not always capable of performing the past actions. In the case of SQL Server, these actions, in general, can be performed.

Where are these options displayed? Well, in the migration in which you defined the one-to-many relationship. Here is an example of a migration where a one-to-many relationship is defined:


protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Contacts",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
Name = table.Column<string>(nullable: true),
Relationship = table.Column<string>(nullable: true),
StudentId = table.Column<int>(nullable: false, defaultValue: 3)
},
constraints: table =>
{
table.PrimaryKey("PK_Contacts", x => x.Id);
table.ForeignKey(
name: "FK_Contacts_Students_StudentId",
column: x => x.StudentId,
principalTable: "Students",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});

migrationBuilder.CreateIndex(
name: "IX_Contacts_StudentId",
table: "Contacts",
column: "StudentId");
}

As we can see in the highlighted line of the previous code, there we can place the referential action that we want to be done at the database level.

Summary

Referential actions are those that tell us what action we want to take in case a record of a table that is referenced by another record of another table is edited or deleted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s