How to remove "auto increment" from a MSSQL table via liquibase

Question

Looking in the Liquibase documentation I can see a ChangeSet action for addAutoIncrement... however the opposite of that (dropAutoIncrement ?) does not seem to exist.

The Problem

In a database table Widget, I have switched from a single-field, auto-increment id (WidgetId) to a composite id (over 3 columns). Adding a new primary index over 3 columns (WidgetId, WidgetType, OrderId) seems to work fine.

Under the new design, my application may explicitly set the WidgetId (e.g.: 1, 2, 3... etc)

However, I am getting the following error when my application inserts new data into the table:

o.h.engine.jdbc.spi.SqlExceptionHelper : Cannot insert explicit value for identity column in table 'Widget' when IDENTITY_INSERT is set to OFF.

Type: int (auto increment)

When I look at the table Wigdets, the type of widgetId is int (auto increment) - this is a legacy from the previous database design (with a database generated unique id) and I presume this is the issue here.

Given the database is MS-SQL and we use liquibase, how can I remove the "auto increment" from WidgetId?

I would like some liquibase xml that codes in here:

    <changeSet author="steve" id="remove-auto-increment-from-widget-id">

    </changeSet>

Our backend is: Liquibase 3.8.1 / JPA / Java / MS-SQL

Answer 1

I did it using the <modifyDataType> tag. That should remove the AUTO_INCREMENT from the column.

<modifyDataType tableName="widget" columnName="widget_id" newDataType="int"/>