How to insert auto_increment in an already created table in liquibase

Question

How to insert auto_increment in an already created table in liquibase?

User table already exists.

<createTable tableName="user">
   <column name="user" type="varchar(255)">
      <constraint nullable="false">
   </column>
   <column name="user_id" type="varchar(255)"/>
   <column name="password" type="varchar(255)"/>
   <column name="email" type="varchar(255)"/>
</createTable>
<addPrimaryKey tableName="user"> columnNames="user" constraintName="user_pk">

Duplicate user occurred in userTable. The pk was removed because the user could have a duplicate name.

However, since we need to have the value of @Id, if we add the ID and set the autoIncrement, there will be a problem.

<dropPrimaryKey tableName="user"/>
<addColumn tableName="user">
    <column name="id" type="BIGINT"/>
</addColumn>
<createSequence
    incrementBy="1" 
    maxValue="9223372036854775807" 
    minValue="1"  
    startValue="1"
    sequenceName="user_sequence"/>

Error Message

Reason: liquibase.exception.DatabaseException: ERROR: column "id" of relation "user" contains null values [Failed SQL: (0) ALTER TABLE "public"."user" ADD CONSTRAINT "PK_user" PRIMARY KEY ("id")]

Seems like you have null values already in your ID column, which is not allowed for primary key columns. You have to fix those first. — jarlh, Aug 29 '22 at 06:26

score 0 · Answer 1 · answered Aug 29 '22 at 09:00

Please check this SO post.

<changeSet id="1" author="Me">
    <dropPrimaryKey tableName="tv_campaigns"/>
    <addColumn tableName="tv_campaigns">
        <column name="ID" type="bigint(20)" autoIncrement="true">
            <constraints nullable="false" primaryKey="true" unique="true"/>
        </column>
    </addColumn>
    <addAutoIncrement tableName="tv_campaigns" columnName="ID" columnDataType="bigint(20)"/>
</changeSet>

Try using the addAutoIncrement tag, read more about it here.

How to insert auto_increment in an already created table in liquibase

1 Answers1