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

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