Liquibase - insert rows with uuid

Question

I have two tables declared as follows:

<changeSet author="istvan" id="country-table-changelog">
    <createTable tableName="country">
        <column name="id" type="uuid">
            <constraints nullable="false" unique="true" />
        </column>
        <column name="name" type="varchar">
            <constraints nullable="false" unique="true" />
        </column>
    </createTable>
</changeSet>

<changeSet author="istvan" id="region-table-changelog">
    <createTable tableName="region">
        <column name="id" type="uuid" >
            <constraints nullable="false" unique="true" />
        </column>
        <column name="country_id" type="uuid">
            <constraints nullable="false" />
        </column>
        <column name="name" type="varchar">
            <constraints nullable="false" unique="true" />
        </column>
    </createTable>
</changeSet>

<changeSet author="istvan" id="region-country-foreign-key-constraint">
    <addForeignKeyConstraint 
        baseTableName="region"
        baseColumnNames="country_id"
        referencedTableName="country"
        referencedColumnNames="id"
        constraintName="fk_region_country"
        onDelete="CASCADE" 
        onUpdate="RESTRICT"/>
</changeSet>

I want to fill both tables from liquibase changelog file with some values like:

INSERT INTO country VALUES('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'HUNGARY');
INSERT INTO region VALUES('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'Baranya');

In the example I used aaaa's and bbbb's just because of simplicity. I want to generate those UUID's by the DBMS.

What is the best way to do it? Do I have to use SQL in my changelog files or is it possible with XML? I prefer DBMS independent solution like XML or JSON.

My second question is that how can I declare a column with UUID that creates the UUID on insert. Something like:

<column name="id" type="uuid" value="??? GENERATE UUID ???">
    <constraints nullable="false" unique="true" />
</column>

Thank you for your time!

score 40 · Accepted Answer · 2019-03-23T09:48:43.933

40

You can do this by using properties that are defined depending on the current DBMS.

<property name="uuid_type" value="uuid" dbms="postgresql"/>
<property name="uuid_type" value="uniqueidentifier" dbms="mssql"/>
<property name="uuid_type" value="RAW(16)" dbms="oracle"/>

<property name="uuid_function" value="uid.uuid_generate_v4()" dbms="postgresql"/>
<property name="uuid_function" value="NEWID()" dbms="mssql"/>
<property name="uuid_function" value="sys_guid()" dbms="oracle"/>

Then use those properties when defining the table:

<column name="id" type="${uuid_type}" defaultValueComputed="${uuid_function}">
    <constraints nullable="false" unique="true" />
</column>

Note that you need to use defaultValueComputed, not value

If the column is defined with a default value, just leave it out in your insert statements and the database will then generate the UUID when inserting.

edited Mar 23 '19 at 09:48

answered Feb 22 '17 at 11:35

1

The first part works. It inserts Hungary with `919d39e4-2387-4926-91d5-595ebe0eea07` id. However, for the second insert it gives the following exception: `ERROR: insert or update on table "region" violates foreign key constraint "fk_region_country" Key (country_id)=(34e4e0d8-91d8-4d52-99ae-ec42d6b0e66e) is not present in table "country"`. Usage of foreign key is clear but I don't know how to work with it in liquibase. Here is the code I use: ` ` Thx – Farkas István Feb 23 '17 at 18:45
@FarkasIstván - I'm receiving the same error. Hopefully I'll find an answer for it soon, but in the meantime, if you have it figured out please let me know. – degreesightdc Aug 15 '17 at 03:13
For Oracle, shouldn't it be `RAW(16)` rather than `RAW(32)` ? (yes, Oracle used to have a bug in their online docs in this respect, but it has been corrected). See https://docs.oracle.com/database/121/SQLRF/functions202.htm#SQLRF06120. – peterh Mar 23 '19 at 09:32
I got it to work with postgres (14.5) when defining the function property with `value="gen_random_uuid()"` (otherwise I got errors about the uid namespace) – traneHead Sep 06 '22 at 20:18

score 4 · Answer 2 · answered Jun 27 '18 at 11:02

4

For MySQL, put your property just before changeSet tag:

    <property name="u_id" value="uuid()" dbms="mysql"/>

then

    <column name="ID" type="varchar(255)" valueComputed="${u_id}"/>

NOTE: here valueComputed is used, not defaultValueComputed.

answered Jun 27 '18 at 11:02

Rammgarot

1,467
14
9

score 1 · Answer 3 · edited May 23 '17 at 11:46

1

Some databases supports UUID columns: Generate UUID values by default for each row on column of UUID type in H2 Database Engine

I don't think that Liquibase has embedded UUID generator, have a look at defaultValueComputed/valueComputed property for column (http://www.liquibase.org/documentation/column.html) + DB function to generate UUID

edited May 23 '17 at 11:46

Community

1
1

answered Feb 22 '17 at 09:13

dbf

6,399
2
38
65

score 0 · Answer 4 · answered Mar 04 '20 at 14:40

0

Like Rammgarot noted, since we are dealing with columns that need to have unique values, we should use valueComputed instead of defaultValueComputed.

answered Mar 04 '20 at 14:40

user2121810

1

score 0 · Answer 5 · answered Mar 09 '21 at 23:12

Any of the provided answers didn't help me in case of MySQL, but I was able to work it out using another approach - using triggers.

<changeSet author="nberezovski" id="1">
        <createTable tableName="test">
            <column name="id" type="varchar(255)">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="description" type="varchar(255)">
            ...
        </createTable>
</changeSet>

<changeSet author="nberezovski" id="2">
        <sql splitStatements="false">
            CREATE TRIGGER insert_test
            BEFORE INSERT ON test
            FOR EACH ROW
            BEGIN
            IF (NEW.id IS NULL) THEN
            SET NEW.id = UUID();
            END IF;
            END;
        </sql>
</changeSet>

After that, each manual insert only of values for description MySQL automatically generated an id for me. For example:

insert into test(description) values('some description');

Also that approach helped in loading data using loadData

score 0 · Answer 6 · answered Sep 14 '22 at 15:52

0

For SQL SERVER works

<column name="uuid" valueComputed="newid()" />

answered Sep 14 '22 at 15:52

andres perez

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 21 '22 at 13:29

score 0 · Answer 7 · answered Jan 27 '23 at 09:40

0

For me helped this (postgres 14.2):

<property name="uuid_function" value="gen_random_uuid()" dbms="postgresql"/>
<column name="id" valueComputed="${uuid_function}"/>

answered Jan 27 '23 at 09:40

Anton

604
2
11
22

Liquibase - insert rows with uuid

7 Answers7

Linked