Informix constraints should not use default names

Description

Informix allocates default names to constraints if they are not explicitly named. The names are of the form xYYY_ZZ where x is a letter identifying the constraint type, YYY is the systables.tabid for the table and ZZ is an incremental counter.

This problem occurred because Liquibase changelogs such names.

In my test database, before I ever use Liquibase, I have a table with tabid 100 and a primary key with the default name u100_1. When I generate a changelog for this database to apply to an empty database, it records the default name. However, when I run liquibase against the empty database to update it, the first thing Liquibase does is create its tables, the first one has a tabid of 100 and it's the first constraint, so it gets called u100_1. Then the update starts applying and dies because it tries to create another constraint with the same name.

The problem here is further that the update is trying to create a constraint with the wrong name anyway, as the tabid 100 is now a liquibase table and not the table it's creating the constraint on.

The suggested fix to this problem is relatively simple: if you have ANY constraint matching

you should not pass its name into the changelog. Or not name the constraint when generating the SQL, I guess.

I suppose you should also explicitly name liquibase's constraints!

Environment

MacOS X, Informix 12.10.FC9DE (but should repro on just about every Informix server ever)

Status

Assignee

Unassigned

Reporter

William Wheeler

Labels

None

Components

Affects versions

Priority

Blocker
Configure