Adding an auto increment unique key using liquibase

user_16213193 0
Viewed 516

I have a table called event and it already has a primary key. I have saved a few records in the event table. Now I wanted to add an auto-increment column for the same table. I created a new changelog.xml file and added the following in the xml.

<changeSet author="user" id="1">
        <addColumn tableName="event">
            <column name="id" type="BIGINT" >
                <constraints nullable="false" unique="true" uniqueConstraintName="event_id_auto_increment" />
            </column>
        </addColumn>
        
        <addAutoIncrement incrementBy="1" tableName="event" columnName="id" columnDataType="BIGINT" />
        
</changeSet>

But I was getting an exception when starting the application.

Caused by: liquibase.exception.DatabaseException: Duplicate entry '0' for key 'id' [Failed SQL: (1062) ALTER TABLE event ADD UNIQUE (id)]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:393)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:82)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:150)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1278)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1260)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)
    ... 169 common frames omitted
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '0' for key 'id'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:389)
    ... 174 common frames omitted

The new column id has been created in the event table with values as 0 for all the existing rows. But wasn't incrementing. Can someone help me with this? Any help would be appreciated. Thanks

liquibase spring-boot mysql
1 Answers

I think the problem is having preexisting data in your table. I suggest you create the column first. Do not add constraint right away.

Run an update statement to update the new column with unique values (e.g., insert the row number into each row for this column).

After you have unique data in this row, then you can add the unique constraint. And also add auto increment to this column.

user_172525150
Related