Primary, Alternate, Composite, and Foreign Keys: Identity and Relationship Semantics

Learning outcomes

ServiceHub already has a stable WorkOrder mapping, but the team now needs identifiers that serve different jobs: an internal surrogate key for tracking, a human-facing work-order number, keys that identify child records, and references that other rows can enforce. Treating every unique value as “basically a primary key” creates subtle EF metadata and database-design mistakes. This lesson separates entity identity from uniqueness and from relationship targets.

1. Primary key means EF identity, not merely uniqueness

An EF Core entity type normally has one primary key. EF uses that key to decide whether two rows materialized into a tracking query represent the same entity instance. The relational database uses the corresponding primary-key constraint to reject duplicate key values. Those are related responsibilities, but they happen in different layers.

For ServiceHub, WorkOrder.Id remains the internal integer primary key. The Chapter 03 configuration already maps it to work_order_id. We now make the intent explicit and name the database constraint so migration diffs are easier to review.

builder.HasKey(x => x.Id)    .HasName("PK_work_orders");builder.Property(x => x.Id)    .HasColumnName("work_order_id")    .ValueGeneratedOnAdd();

ValueGeneratedOnAdd and key generation are covered deeply in Lesson 3. For now, the important point is that Id is the identity EF uses for tracked WorkOrder instances.

2. Add a stable business identifier as an alternate key

A dispatcher needs a durable external identifier such as WO-2026-000184. It should be unique and it may eventually be referenced by integration tables. That makes it a reasonable candidate for an alternate key: an additional key in EF metadata that can be the principal target of a foreign key.

public sealed class WorkOrder{    private string _summary = string.Empty;    private WorkOrder() { }    public WorkOrder(        string workOrderNumber,        string customerName,        string summary,        WorkOrderPriority priority,        DateTimeOffset openedUtc)    {        WorkOrderNumber = string.IsNullOrWhiteSpace(workOrderNumber)            ? throw new ArgumentException("Work-order number is required.", nameof(workOrderNumber))            : workOrderNumber.Trim();        CustomerName = customerName.Trim();        Priority = priority;        OpenedUtc = openedUtc;        ReviseSummary(summary);    }    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public string Summary => _summary;    public WorkOrderPriority Priority { get; private set; }    public DateTimeOffset OpenedUtc { get; private set; }    public string DisplayLabel => $"{WorkOrderNumber} - {CustomerName}: {Summary}";    public void ReviseSummary(string value) { /* Chapter 03 invariant */ }}

builder.Property(x => x.WorkOrderNumber)    .HasColumnName("work_order_number")    .HasMaxLength(32)    .IsRequired();builder.HasAlternateKey(x => x.WorkOrderNumber)    .HasName("AK_work_orders_work_order_number");

Microsoft's EF Core key documentation makes an important distinction: if you only need uniqueness, prefer a unique index. An alternate key adds EF semantics because it can be a principal key for relationships and is treated as a key, not merely as an access-path/index declaration.

3. Foreign key points from dependent identity to principal identity

A foreign key is one or more properties on a dependent entity whose values identify a principal entity. Chapter 05 will cover relationship cardinality, navigations, requiredness, cascades, and fixup in depth. Here the narrower goal is to see how a foreign key can target either the primary key or an alternate key.

public sealed class WorkOrderNote{    public long Id { get; set; }    public string WorkOrderNumber { get; set; } = string.Empty;    public string Text { get; set; } = string.Empty;}modelBuilder.Entity<WorkOrderNote>(note =>{    note.HasKey(x => x.Id);    note.HasOne<WorkOrder>()        .WithMany()        .HasForeignKey(x => x.WorkOrderNumber)        .HasPrincipalKey(x => x.WorkOrderNumber);});

HasPrincipalKey tells EF that this relationship targets WorkOrder.WorkOrderNumber instead of WorkOrder.Id. EF introduces or uses alternate-key metadata for that principal value. A unique index alone would enforce uniqueness in the database, but it would not by itself declare an EF principal key.

4. Composite keys identify rows with more than one value

Some records are naturally identified by a tuple. A checkpoint inside one work order can use (WorkOrderId, Sequence): sequence 1 only has meaning inside its parent work order. EF requires composite key property order to be explicit and consistent with foreign-key ordering.

public sealed class WorkOrderCheckpoint{    public int WorkOrderId { get; set; }    public int Sequence { get; set; }    public DateTimeOffset OccurredUtc { get; set; }    public string Status { get; set; } = string.Empty;}modelBuilder.Entity<WorkOrderCheckpoint>(checkpoint =>{    checkpoint.ToTable("work_order_checkpoints");    checkpoint.HasKey(x => new { x.WorkOrderId, x.Sequence });});

Composite keys are not automatically “more domain correct” than surrogate keys. They affect every referencing foreign key, API payload, cache key, and migration. Use them where the tuple really is stable identity, not because a table happens to contain two columns that look unique today.

5. Keyless entity types model query shapes, not persisted identity

Some relational results do not have a stable key: a database view, a reporting projection, or a read-only query shape may legitimately have no entity identity. EF supports keyless entity types. They are never tracked for changes and are not inserted, updated, or deleted through normal SaveChanges persistence.

public sealed class OpenWorkOrderSummary{    public string Priority { get; init; } = string.Empty;    public int OpenCount { get; init; }}modelBuilder.Entity<OpenWorkOrderSummary>(report =>{    report.HasNoKey();    report.ToView("open_work_order_summary");});

Do not use HasNoKey as an escape hatch because you forgot to identify an entity correctly. It changes EF behavior fundamentally.

6. Inspect the finalized key metadata

var entity = db.Model.FindEntityType(typeof(WorkOrder))!;Console.WriteLine($"Primary key: {string.Join(", ", entity.FindPrimaryKey()!.Properties.Select(p => p.Name))}");foreach (var key in entity.GetKeys()){    Console.WriteLine($"Key: {key.GetName() ?? "<unnamed>"}");    Console.WriteLine($"  Properties: {string.Join(", ", key.Properties.Select(p => p.Name))}");    Console.WriteLine($"  Primary: {key == entity.FindPrimaryKey()}");}foreach (var fk in db.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys())){    Console.WriteLine($"FK {fk.DeclaringEntityType.DisplayName()} -> {fk.PrincipalEntityType.DisplayName()}");    Console.WriteLine($"  Dependent: {string.Join(", ", fk.Properties.Select(p => p.Name))}");    Console.WriteLine($"  Principal: {string.Join(", ", fk.PrincipalKey.Properties.Select(p => p.Name))}");}

This is stronger evidence than reading one configuration method. It tells you what survived conventions, configuration precedence, and model finalization.

7. Identity resolution depends on keys

In a tracking query, if EF encounters the same primary-key value again, it resolves that row to the already tracked entity instance rather than constructing an independent tracked duplicate. That identity-map behavior is one reason key correctness matters even before a database write occurs.

var first = await db.WorkOrders.SingleAsync(x => x.Id == 1, ct);var again = await db.WorkOrders.SingleAsync(x => x.Id == 1, ct);Console.WriteLine(ReferenceEquals(first, again)); // True in the same tracking contextConsole.WriteLine(db.ChangeTracker.Entries<WorkOrder>().Count());

This does not mean every query everywhere returns the same CLR object. No-tracking queries, different contexts, projections, and later loading strategies have different identity behavior. Chapter 11 treats those distinctions directly.

8. Deliberately wrong approach: mutate a tracked primary key

A developer sees Id as “just an integer property” and tries to renumber an existing work order. Key properties define identity; changing them is not the same as changing a summary. EF normally rejects attempts to modify a tracked key because the operation would require changing entity identity and potentially every dependent reference.

var workOrder = await db.WorkOrders.SingleAsync(x => x.Id == 1, ct);// Do not do this. A key is identity, not normal mutable state.db.Entry(workOrder).Property(x => x.Id).CurrentValue = 9001;await db.SaveChangesAsync(ct);

The exact exception text is version-dependent, so production diagnostics should log the exception type/message rather than test one sentence. The repair is architectural: keep surrogate primary keys immutable. If a business identifier can legitimately change, model it as non-key data or design an explicit migration/re-key workflow with all dependent consequences understood.

9. Hands-on lab: evolve ServiceHub identity safely

1. Start from the Chapter 03 WorkOrderConfiguration without changing existing table/column mappings.
2. Add WorkOrderNumber to WorkOrder and to the deterministic seed constructor calls.
3. Map it to work_order_number, max length 32, required.
4. Configure an alternate key and name the constraint.
5. Add the small WorkOrderNote example that targets the alternate key; keep relationship scope minimal because Chapter 05 handles relationship design.
6. Add a temporary WorkOrderCheckpoint type with the composite key shown above.
7. Generate a migration and review the unique/key/foreign-key operations before applying to the disposable SQLite lab.
8. Run the runtime metadata probe and save its output.
9. Attempt a tracked-key mutation in disposable data, record the failure, then reset the database.

dotnet tool run dotnet-ef -- migrations add Chapter04Identity --project src/ServiceHub.EfLabdotnet tool run dotnet-ef -- migrations script --project src/ServiceHub.EfLab# Review generated operations first.dotnet tool run dotnet-ef -- database update --project src/ServiceHub.EfLab

Verification checklist

• WorkOrder.Id remains the primary key mapped to work_order_id.
• WorkOrderNumber is an alternate key, not a duplicate primary key.
• The dependent example targets the intended principal key.
• The composite key contains both properties in a documented order.
• The runtime model and generated migration agree.
• No migration operation accidentally drops/recreates Chapter 03 mappings.

10. Production judgment and bridge

Choose keys for identity stability first, convenience second. Surrogate keys keep references compact; alternate keys are valuable when another stable identifier must be a relationship target; unique indexes are preferable when uniqueness is the only requirement; composite keys are useful when the tuple really is identity; keyless types are for query-only shapes.

Lesson 2 moves from identity constraints to access paths. You will configure indexes, sort order, uniqueness, provider-specific filters/includes, and then prove what SQLite actually created rather than assuming every provider interprets index metadata identically.

Authoritative references

• Keys - EF Core — primary, alternate, composite, generated-key, and keyless identity guidance
• Foreign and principal keys — foreign-key and alternate-principal-key configuration
• Keyless Entity Types — read-only keyless model semantics
• Change Tracking in EF Core — entity identity/state tracking background
• Microsoft.EntityFrameworkCore 10.0.11 — current stable EF Core 10 package checkpoint