Cannot add a non-primary key identity column

Question

So I created a table named tv_campaigns in liquibase as follows.

<changeSet id=" add tv campaigns table" author="abc">
        <createTable tableName="tv_campaigns">
            <column name="campaign_id" type="int" autoIncrement="false">
                <constraints primaryKey="true"
                             foreignKeyName="fk_linear_tv_campaign_id"
                             references="campaigns(id)"
                             nullable="false"/>
            </column>
        </createTable>
    </changeSet>

Here the primary and foreign key is the same (campaign_id) and the table just have one column. Later I realized I need an auto-increment column in this table and proceeded to add this changeset:

<changeSet id="add auto increment column" author="abc">
        <addColumn tableName="tv_campaigns">
            <column name="id" type="int" autoIncrement="true">
                <constraints unique="true"
                             nullable="false"/>
            </column>
        </addColumn>
    </changeSet>

On running this changeset I get the following error: "Cannot add a non-primary key identity column". Tried adding 'id' as a primary key by dropping the older primary key (campaign_id) and that too throws an error about foreign key relation not properly defined (as the original primary key also references another table's key).

Is there a way to do it nicely or to do it?

Why not to remove FK constraint from campaign_id first, then remove primary key from tv_campaigns, then set primary key to id column and after it foreign key to campaign_id? — dbf, Feb 17 '16 at 14:16

score 1 · Answer 1 · answered Nov 27 '19 at 15:01

I believe your existing column is considered the primary key. In order to check it, see the indexes tab in HeidiSQL or similar. Some more information on the subject you can find here Add Auto-Increment ID to existing table?

The following changeset should work:

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

score 0 · Answer 2 · answered Feb 20 '16 at 16:29

It sounds like a restriction imposed by MySQL. Which is not surprising, really. Are you able to make the same change using SQL statements? If so then it should be possible via Liquibase. But if you can't do it with SQL, then you can't do it with Liquibase either.

Cannot add a non-primary key identity column

2 Answers2

Linked