Creating tables

Describe a complete table: columns first, with explicit named keys and constraints.

Examples use Classic and Fluent tabs. Your choice follows you through the manual.

Create a table with a key

The table definition groups related schema objects into one operation. Primary-key columns are emitted as non-nullable. In the fluent API a complete table is collected before execution, so keys can refer to columns declared in the same chain. WithColumn returns a builder bound to that specific column. Table-level methods such as WithPrimaryKey return the table builder; call WithColumn again before supplying more column options.

Classic

using System.Data;
using DotNetProjects.Migrator.Framework;

[Migration(1)]
public class CreateUsers : Migration
{
    public override void Up()
    {
        Database.AddTable("Users",
            new Column("Id", DbType.Int32) { IsNullable = false },
            new Column("Name", DbType.String, 255),
            new PrimaryKeyConstraint("PK_Users", "Id"));
    }

    public override void Down() => Database.RemoveTable("Users");
}

Fluent

using DotNetProjects.Migrator.Framework;
using DotNetProjects.Migrator.Framework.Fluent;

[Migration(1)]
public class CreateUsers : FluentMigration
{
    public override void BuildUp(MigrationBuilder migration)
    {
        migration.Create.Table("Users")
            .WithColumn("Id").AsInt32().NotNullable()
            .WithColumn("Name").AsString(255)
            .WithPrimaryKey("PK_Users", "Id");
    }

    public override void BuildDown(MigrationBuilder migration)
        => migration.Delete.Table("Users");
}

Choose one authoring style

Composite keys and uniqueness

Use the declared key order consistently in both primary and foreign keys. A composite unique constraint applies to the tuple; it does not make each column unique separately. The fully qualified constraint type below avoids the name collision with System.Data.UniqueConstraint.

Classic

Database.AddTable("Subscriptions",
    new Column("TenantId", DbType.Int32),
    new Column("UserId", DbType.Int32),
    new Column("Email", DbType.String, 255),
    new PrimaryKeyConstraint("PK_Subscriptions", "TenantId", "UserId"),
    new DotNetProjects.Migrator.Framework.UniqueConstraint(
        "UQ_Subscriptions_Email", "TenantId", "Email"));

Fluent

migration.Create.Table("Subscriptions")
    .WithColumn("TenantId").AsInt32()
    .WithColumn("UserId").AsInt32()
    .WithColumn("Email").AsString(255)
    .WithPrimaryKey("PK_Subscriptions", "TenantId", "UserId")
    .WithUniqueConstraint("UQ_Subscriptions_Email", "TenantId", "Email");

Inside Up() / BuildUp(MigrationBuilder migration)

Identity and removal

Identity generation is a column attribute, separate from primary-key membership. SQLite requires an INTEGER identity column and its single-column primary key in the same definition. Use a complete Create.Table/AddTable operation to satisfy that rule. To reverse creation use Database.RemoveTable or migration.Delete.Table; dropping a table also removes its rows.

For supported creation operations, automatic reversal can derive the reverse operation. Explicitly author reverse behavior for destructive changes.

Implementation reference ↗Improve this page ↗