Relationships and enforced foreign keys

Object navigation and relational identity must agree

A post’s Blog reference lets application code traverse an object graph, while BlogId records the related principal key. The model explicitly declares that relationship and chooses Restrict deletion so the classroom does not conceal cascading behavior in conventions. Saving a tracked graph can populate generated keys and foreign-key values, but it is still the relational schema that rejects an orphan inserted by another path. Early application checks are useful feedback, not substitutes for that final invariant. Treat entity configuration, the created schema and the actual stored rows as three things that must agree.

Verify the graph and then attempt an actual orphan

The external connection explicitly enables SQLite foreign keys and stays open for the whole fixture. EF saves Blog A with one Post, and an Include query verifies both the generated BlogId and materialized principal name. A separate context then attempts BlogId 9999 without a corresponding principal. The exception filter requires DbUpdateException wrapping SQLite’s foreign-key constraint classification, rather than declaring every save error an expected orphan rejection. A fresh context confirms that the original post remains the only row. This is a genuine database constraint check with SQL execution, not a test double implementing expected list behavior.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var db = new LabDb(options))
{
    await db.Database.EnsureCreatedAsync(); db.Blogs.Add(new Blog { Name = "A", Posts = { new Post { Title = "First" } } }); await db.SaveChangesAsync();
    var row = await db.Posts.Include(p => p.Blog).SingleAsync(); Check(row.BlogId > 0 && row.Blog.Name == "A", "saved FK graph"); Console.WriteLine("related:" + row.Blog.Name);
}
bool rejected = false;
await using (var invalid = new LabDb(options))
{
    invalid.Posts.Add(new Post { Title = "Orphan", BlogId = 9999 });
    try { await invalid.SaveChangesAsync(); }
    catch (DbUpdateException ex) when (ex.InnerException is SqliteException { SqliteErrorCode: 19, SqliteExtendedErrorCode: 787 }) { rejected = true; }
}
await using (var verify = new LabDb(options)) Check(rejected && await verify.Posts.CountAsync() == 1, "foreign key rejected without orphan");
Console.WriteLine("foreign-key:rejected");

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

A supplied-reference check remains only early validation

The BCL practice receives parent IDs one and two and checks child references one and nine. Its known parent and unknown parent outputs follow HashSet membership under those supplied facts. The set does not observe a database or prevent another operation from deleting a parent after validation. It also does not implement EF relationship fixup, cascade rules or generated keys. Use the check to describe a caller’s input contract, then verify the persisted invariant with the real relational provider. Two checks producing the same rejection label can still protect different boundaries and have different race behavior.

Deleting principals and loading navigations are separate policies

Restrict, cascading deletion and optional foreign keys represent different data-retention decisions. Do not infer deletion behavior merely from a nullable C# navigation or from one object graph that happens to remain in memory. A navigation also is not proof that related data was queried; tracking fixup can populate some relationships from already tracked entities. This lesson uses fresh scopes and an explicit Include for its verification. Database providers can differ in constraint timing, supported cascade paths and error details. Preserve SQLite-specific exception handling locally rather than reusing its numeric code as a provider-independent domain API.

Practice: inspect the chosen delete rule on real storage

Using a new disposable fixture, attempt to delete the principal while its required dependent still exists and record the failure under the declared Restrict policy. Then create a separately configured cascade variation and verify both stored counts from a fresh context after deletion. Do not alter an existing database’s model and assume its schema changed automatically. Recreate the transient fixture or use an actual migration for the new schema. Keep orphan insertion, principal deletion and navigation loading as independent cases so one relationship behavior cannot stand in for another.

• Hint: changing fluent configuration does not retroactively modify an existing schema.
• Hint: verify both principal and dependent rows after the operation.

Practice: validate a complete batch before accepting references

Extend the supplied-reference exercise with duplicate principal IDs, several unknown references and a repeated dependent. Decide whether to reject the whole batch or return structured row-specific errors without mutating accepted state. That decision can be checked with ordinary collections, but it must not be advertised as a relational commit. Add a case where a parent disappears after the initial input snapshot and explain what the database constraint still contributes. Keep identity normalization explicit if identifiers become strings, since application equality and a database’s collation do not automatically share the same semantics.

• Hint: validation policy and storage enforcement protect different moments.
• Hint: name unknown-reference and duplicate-identity failures separately.

Keep configuration, enforcement and observation together

The complete external check establishes a saved relationship and a rejected orphan using native SQLite with foreign keys enabled. The portable result establishes only supplied membership. Neither proves another provider’s constraint behavior or a production migration’s correctness. A useful review names principal and foreign keys, nullability, delete behavior, schema creation strategy and the fresh query used to observe the result. Those facts remain valuable when a larger application uses several contexts or writers, because navigation convenience alone cannot supply the durable referential invariant that the storage layer is meant to enforce.