0

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)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lisa G
  • 1
  • 2
  • 2
    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
  • The other question is why a *negative* increment? – Panagiotis Kanavos Jan 11 '19 at 10:40
  • 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

0 Answers0