.PrimaryKey() doesn't work on Alter.Table() syntax root

[jzabroski]

Describe the bug
.PrimaryKey() doesn't work on Alter.Table() syntax root. .PrimaryKey() does work on Create.Table() syntax root.

This probably doesn't come up very often, but in my scenario, I was fixing a table someone created using natural keys and making it use synthetic keys created via IDENTITY auto-incrementing values.

SQL Server does support the underlying syntax to make this easy:

ALTER TABLE dbo.Example
   ADD ExampleId INT IDENTITY
       CONSTRAINT PK_Example PRIMARY KEY CLUSTERED

To Reproduce

using FluentMigrator;

namespace Database.Migrations
{
    [Migration(0)]
    public class M0 : ForwardOnlyMigration
    {
        public override void Up()
        {
            Create.Table("Example")
                .InSchema("dbo")
                .WithColumn("ExampleId")
                .AsInt64().NotNullable()
                .PrimaryKey("PK_Example")
                .Identity()
                .WithColumn("ExtraColumn")
                .AsBoolean().NotNullable();

            Delete.PrimaryKey("PK_Example")
                .FromTable("Example")
                .InSchema("dbo");

            Delete.Column("ExampleId")
                .FromTable("Example")
                .InSchema("dbo");

            Alter.Table("Example")
                .InSchema("dbo")
                .AddColumn("ExampleId")
                .AsInt64().NotNullable()
                .PrimaryKey("PK_Example")
                .Identity();
        }
    }
}

This generates the following SQL in SQL Profiler:

CREATE TABLE [dbo].[Example] ([ExampleId] BIGINT NOT NULL IDENTITY(1,1), [ExtraColumn] BIT NOT NULL, CONSTRAINT [PK_Example] PRIMARY KEY ([ExampleId]))

ALTER TABLE [dbo].[Example] DROP CONSTRAINT [PK_Example]

DECLARE @default sysname, @sql nvarchar(max);

-- get name of default constraint
SELECT @default = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('[dbo].[Example]')
AND type = 'D'
AND parent_column_id = (
SELECT column_id
FROM sys.columns
WHERE object_id = object_id('[dbo].[Example]')
AND name = 'ExampleId'
);

-- create alter table command to drop constraint as string and run it
SET @sql = N'ALTER TABLE [dbo].[Example] DROP CONSTRAINT ' + QUOTENAME(@default);
EXEC sp_executesql @sql;

-- now we can finally drop column
ALTER TABLE [dbo].[Example] DROP COLUMN [ExampleId];

ALTER TABLE [dbo].[Example] ADD [ExampleId] BIGINT NOT NULL IDENTITY(1,1)

INSERT INTO [Monitor].[VersionInfo] ([Version], [AppliedOn], [Description]) VALUES (0, '2021-03-16T15:13:16', 'M0')

Expected behavior
A clear and concise description of what you expected to happen.

Information (please complete the following information):

• OS:
  • OS Name: Microsoft Windows Server 2016 Datacenter
  • OS Version: 10.0.14393 N/A Build 14393
• Platform: net48
• FluentMigrator version: 3.2.1
• FluentMigrator runner: Tested on FluentMigrator.MSBuild and FluentMigrator.DotNet.Cli
• Database Management System: SqlServer2014
• Database Management System Version: SQL Server 14.0.1000.169 (SQL Server 2017 RTM)

Additional context
Sample .targets file:

<?xml version="1.0" encoding="utf-8" ?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Build">
  <PropertyGroup>
    <FluentMigratorMSBuildAssemblyPath>path/to/FluentMigrator.MSBuild.dll</FluentMigratorMSBuildAssemblyPath>
  </PropertyGroup>

  <UsingTask TaskName="FluentMigrator.MSBuild.Migrate" AssemblyFile="$(FluentMigratorMSBuildAssemblyPath)" Condition="$(FluentMigratorMSBuildAssemblyPath) != ''" />
  <!-- Target: DbMigrateCore -->
  <Target Name="DbMigrateCore">
    <Error Text="Server property is required!" Condition="$(Server) == ''" />
    <Error Text="Catalog property is required!" Condition="$(Catalog) == ''" />
    <Error Text="Assembly property is required!" Condition="$(Assembly) == ''" />
    <Error Text="Task property is required!" Condition="$(Task) == ''" />
    <Error Text="FluentMigratorMSBuildAssemblyPath property is required!" Condition="$(FluentMigratorMSBuildAssemblyPath) == ''" />

    <PropertyGroup>
      <ConnectionString Condition="$(Username) == ''">Data Source=$(Server)%3BInitial Catalog=$(Catalog)%3BIntegrated Security=SSPI</ConnectionString>
      <ConnectionString Condition="$(Username) != ''">Data Source=$(Server)%3BInitial Catalog=$(Catalog)%3BUser ID=$(Username)%3BPassword=$(Password)</ConnectionString>
      <Processor>SqlServer2014</Processor>
      <Timeout>6000</Timeout>
      <Verbose>True</Verbose>
      <StripComments>False</StripComments>
    </PropertyGroup>

    <Message Importance="High" Text="Running FluentMigrator task [$(Task)] against [$(ConnectionString)] with assembly [$(Assembly)].  Profile=[$(Profile)] Tags=[$(Tags)]" />

    <Migrate Database="$(Processor)"
             Connection="$(ConnectionString)"
             Target="$(Assembly)"
             Profile="$(Profile)"
             Tags="$(Tags)"
             Timeout="$(Timeout)"
             StripComments="$(StripComments)"
             Verbose="$(Verbose)"
             Task="$(Task)"/>

    <!--
    <Exec Command="dotnet-fm migrate - -processor $(Processor) - -connection &quot;$(ConnectionString)&quot; - -assembly &quot;$(Assembly)&quot; - -profile &quot;$(Profile)&quot; - -tag &quot;$(Tags)&quot; - -timeout $(timeout) - -verbose" WorkingDirectory="$(ToolsDirectory)\packages" />
    -->
  </Target>

  <!-- Target: DbMigrate -->
  <Target Name="DbMigrate">
    <!-- Use CallTarget vs DependsOnTargets to keep the Arguments variable scope limited to this target -->
    <Message Importance="High" Text="--> Calling Build" />
    <CallTarget Targets="Build" />

    <Message Importance="High" Text="Running migrations against the following databases:" />
    <Message Importance="High" Text="Environment:$(Environment) Server:%(Arguments.Server) Catalog:%(Arguments.Catalog) Tags:%(Arguments.Tags)" />

    <MSBuild Projects="$(MSBuildProjectFile)"
             Targets="DbMigrateCore"
             Properties="
             Server=$(Server);
             Catalog=$(Catalog);
             Username=$(Username);
             Password=$(Password);
             Assembly=$(AssemblyPath);
             Task=migrate:up;
             Tags=$(Tags);" />
  </Target>
</Project>

[IB38]

This came up for me recently.
Is there a reason for this behavior?

[jzabroski]

From a technical literal perspective, the reason this is an issue is that the tagless final interpreter GenericGenerator does not implement it but the interface suggests it's possible.

From a technical SQL perspective, ANSI SQL, there is no direct analog for this because Primary keys cannot be nullable, so there is limited value in the standard providing syntax to add a column with primary key in one shot, given the table would have to have zero rows.

From a technical Fluentmigrator perspective, it would be nice to:

1. Address null values with a seeding script
2. alter the column to be not nullable and add a primary key

From a human perspective, someone has to do the work and submit a PR. I'm not paid for my work here, and this is a relatively mild annoyance.