Liquibase: How do I set the autoincrement seed and increment in SQL Server

Question

I have a table:

CREATE TABLE dbo.Courses
(
    courseID BIGINT IDENTITY(-1,-1) NOT NULL,
    courseName NVARCHAR(100) NOT NULL
);

I am trying to convert this to liquibase

  - createTable:
    tableName: Courses
    columns:
    - column:
        name: courseID
        type: bigint
        autoIncrement: true
    - column:
        name: courseName
        type: nvarchar(100)
        constraints:
            nullable: false

The liquibase code generates

CREATE TABLE Courses
(
      courseID BIGINT IDENTITY (1, 1), 
      courseName NVARCHAR(100) NOT NULL
)
GO

I tried not setting autoIncrement: true and then adding addAutoIncrement after the create table but that returned:

ERROR liquibase.integration.commandline.Main - Unexpected error running Liquibase: Validation Failed:
1 changes have validation failures
addAutoIncrement is not supported on mssql, baselineTables.yml::Courses::user

Next I tried to change it manually using T-SQL:

ALTER TABLE Courses 
    ALTER COLUMN CourseID IDENTITY (-1, -1)

But I get a SQL Server error.

Any idea how I can get liquibase to set an identity seed and increment to anything but (1,1)?

Possible duplicate of [How do I reset an increment identity's starting value in SQL Server](https://stackoverflow.com/questions/16971/how-do-i-reset-an-increment-identitys-starting-value-in-sql-server) — Ilyes, Jan 11 '19 at 10:34
Just edit the creation script and use the seed you want. Just because a tool used `1` as the seed doesn't mean you can't use another. **Why** do you want to start from -1 though? If you want to store some well known rows for IDs -, -1, -2 etc, it's far better to specify the ID values explicitly in the `INSERT` statements. You can use [SET IDENTITY_INSERT](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017) off and then on to allow inserting specific IDs in a session — Panagiotis Kanavos, Jan 11 '19 at 10:38
I have negative identity values since I am merging two autoincrement tables together into one table and I need unique ids. I don't really want to reset the identity column I want liquibase to set it correctly when I create the table. The best I can come up with so far is to use the sql tag to create the table the way I wanted to. But liquibase should have an option to set the autoincrement seed and increment for mssql. — Lisa G, Jan 11 '19 at 10:58

Liquibase: How do I set the autoincrement seed and increment in SQL Server

0 Answers0