'Liquibase: I have a table with 4 columns. I want to add another column called UUID. How to generate values that match Java UUID for existing objects?

I have a table with 4 columns. I want to add another column called UUID. How to generate values that match java UUID for existing objects? For example, Present database status

ID NAME   AGE GENDER
1  Arnold 12   MALE
2  Keith  14   MALE

When I add UUID column

ID NAME   AGE GENDER UUID
1  Arnold 12   MALE  Null
2  Keith  14   MALE  Null

What i want:

ID NAME   AGE GENDER UUID
1  Arnold 12   MALE  "70f3c2d0-b5b9-11ec-b909-0242ac120002"
2  Keith  14   MALE  "70f3c528-b5b9-11ec-b909-0242ac120002"
javasqljpaliquibaseuuid


Solution 1:[1]

Firstly, I wouldn't recommend naming the database column uuid, as it might be a reserved word in some databases. I'd recommend something like table_name_key.

Regarding your question, the logic could be as follows:

<changeSet author="foo" id="bar_1">
    <preConditions onFail="MARK_RAN">
        <tableExists tableName="your_table"/>
        <not>
            <columnExists tableName="your_table" columnName="your_table_key"/>
        </not>
    </preConditions>
    <comment>Add your_table_key column to your_table table.</comment>
    <addColumn tableName="your_table">
        <column name="your_table_key" type="varchar(36)"/>
    </addColumn>
</changeSet>    

<changeSet author="foo" id="bar_2">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="your_table_key"/>
    </preConditions>
    <comment>Populate your_table.your_table_key values.</comment>
    <update tableName="your_table">
        <column name="your_table_key" valueComputed="uuid()"/>
    </update>
</changeSet>

<changeSet author="foo" id="bar_3">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="your_table_key"/>
        <not>
            <indexExists indexName="idx_your_table_key_unique"/>
        </not>
    </preConditions>
    <comment>Add unique constraint to your_table.your_table_key</comment>
    <addUniqueConstraint tableName="your_table" columnNames="your_table_key"
                         constraintName="idx_your_table_key_unique"/>
</changeSet>

<changeSet author="foo" id="bar_4">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="your_table_key"/>
    </preConditions>
    <comment>Add not null constraint to your_table.your_table_key</comment>
    <addNotNullConstraint tableName="your_table" columnName="your_table_key"
                          columnDataType="varchar(36)"/>
</changeSet>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions