How to add a new column/field to an existing composite primary key

General Discussion
1

Cross-post from StackOverflow.

We have an existing table, with 2 existing columns that serve as a composite primary key. They were set up very similarly to this:

<!-- 🛑 WE CAN NO LONGER MODIFY THIS CHANGESET 🛑 -->

<changeSet author="an_awesome_author" id="EXAMPLE-01">
  <createTable tableName="our_awesome_table">
    <column name="source" type="varchar(32)">
      <constraints 
          primaryKey="true" 
          nullable="false" 
          primaryKeyName="CPK_OUR_AWESOME_TABLE"/>
    </column>
    <column
        name="external_id"
        type="varchar(255)">
      <constraints 
          primaryKey="true" 
          nullable="false"  
          primaryKeyName="CPK_OUR_AWESOME_TABLE"/>
    </column>
  </createTable>
</changeSet>

It has subsequently come to our attention that there can be multiple records with the same source & external_id (twitching eye). The group submitting the data submits on a regular schedule and they’ve assured us that they never use the same combination of source & external_id in the same batch of data.

So (for reasons beyond this question) we’ve decided that we’re going to add a created_at field, that is non-nullable and defaults to when each record is added to the table. That’s not that difficult, e.g. we can run this pair of changeSets

<!-- WE ARE TRYING TO FINALIZE THESE CHANGESETS -->

<changeSet author="yours_truly"
           id="EXAMPLE-02-01">
  <addColumn tableName="our_awesome_table">
    <column name="CREATED_AT"
            type="datetime"
            valueDate="current_datetime"
            defaultValueDate="current_datetime" />
    <!-- the above:
      adds the new field
      sets the type
      adds the current timestamp to existing records
      will default to the current timestamp for new records
    -->
  </addColumn>
</changeSet>
<changeSet author="yours_truly"
           id="EXAMPLE-02-02">
  <addNotNullConstraint tableName="our_awesome_table"
                        columnName="CREATED_AT"/>
  <!-- the above:
    now that every record has a value in the newly created field,
    we can add a constrant preventing null entries into the column
  -->
</changeSet>

BUT, we also need to add created_at to our existing composite primary key!

If I try to include the primary key as a constraint when we’re creating the field it complains about the table only being able to have one primary key —despite the fact that it’s already a composite key and I’m only trying add this to the already in existence composite key. But it also complains about the created_at timestamp being null. So, I’m not sure which is the actual problem :man_shrugging:, but it definitely doesn’t work here.

e.g.

<changeSet author="yours_truly"
           id="EXAMPLE-02-01">
  <addColumn tableName="our_awesome_table">
    <column name="CREATED_AT"
            type="datetime"
            valueDate="current_datetime"
            defaultValueDate="current_datetime">
      <constraints primaryKey="true"
                   primaryKeyName="PK_OUR_AWESOME_TABLE"/>
      <!-- this constraints causes multiple problems -->
    </column>
  </addColumn>
</changeSet>

results in something like:

[ERROR]      Reason: liquibase.exception.DatabaseException: ORA-02260: table can have only one primary key
[ERROR]  [Failed SQL: ALTER TABLE OUR_AWESOME_TABLE.OUR_AWESOME_TABLE ADD CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP PRIMARY KEY NOT NULL]

If I try to include the primary key as a <constraint> in the changeSet where we add the non-nullable constraint then I’m basically told that I can’t use <constraints> there and instead need to one of a list of <addXxxConstraint> and in the list is an <addPrimaryKey> —this sounds like the right constraint.

So if I change the second changeSet to something like this, I would expect it to work:

<changeSet author="yours_truly"
           id="EXAMPLE-02-02">
  <addNotNullConstraint tableName="our_awesome_table"
                        columnName="CREATED_AT"/>
  <addPrimaryKey tableName="our_awesome_table"
                 columnNames="CREATED_AT"
                 primaryKeyName="CPK_OUR_AWESOME_TABLE"/>
</changeSet>

But —as you can probably guess— it doesn’t work. Instead, it informs me that, cvc-complex-type.3.2.2: Attribute 'primaryKeyName' is not allowed to appear in element 'addPrimaryKey'.

So, how can I add a new non-nullable field, to an existing composite primary key?

2

The way I’ve done it is drop the primary key, add the new column with the not null constraint and then recreate the primary key with the new column. I know drop/recreate is a requirement in Oracle. I’m not sure if other platforms allow you to alter a primary key. Based on your examples above, that might look like this:

 <changeSet author="yours_truly" id="EXAMPLE-02-01-dropPK">
    <dropPrimaryKey  tableName= "our_awesome_table" constraintName="CPK_OUR_AWESOME_TABLE"/>
 </changeSet>
 
 <changeSet author="yours_truly" id="EXAMPLE-02-01-addNNcol"> 
     <addColumn tableName="our_awesome_table">
         <column name="CREATED_AT" type="datetime" valueDate="current_datetime" defaultValueDate="current_datetime">
            <constraints nullable="false"/>
       </column>
    </addColumn>
</changeSet>

<changeSet author="yours_truly" id="EXAMPLE-02-02">
    <addPrimaryKey tableName="our_awesome_table" columnNames="source,external_id,CREATED_AT" constraintName="CPK_OUR_AWESOME_TABLE"/>
</changeSet>

Hope this helps!

1 Like
3

Thank you so much!

I had figured out a somewhat similar approach, but my version was nowhere near as clean and elegant as your version; more importantly, I really wanted the composite key to be named, but didn’t realize that I could do that with constraintName (The liquibase docs provides really good very basic examples, but would really benefit from more complex examples.)

Again, thank you so much @Pete!

2 Likes