Create and manage primary keys

Creating a table is one of the most basic elements of Data Definition Language (DDL). Or a phrase commonly used would be better. There is no useful relational database without tables. Even though tables are very common, creating them isn't the most straightforward operation you can think of. One of the reasons is a primary key. How to create a table with a primary key? How to add or modify a primary key later when a table already exists?

What is the primary key?

A primary key is a constraint on a table that defines a set of columns, whose values uniquely identify a row in the table.

A simple example is a table with users, that stores their id, first name and last name. An obvious candidate for a primary key is a single column US_ID, assuming it is unique in the whole table. For example, it may be automatically generated when a new row is inserted.

In the above example, the primary key may be described like that:

primary key on USERS (US_ID)

Another example is a composite primary key. It is a primary key, but is created on more than one column. A good example might be a table with cities.

There is no single column that guarantees uniqueness. Only all three: country, postal code and name identify a row in the table. In short, it might be described like that:

primary key on CITIES (CI_COUNTRY, CI_POSTAL_CODE, CI_NAME)

Create table with primary key

Single column PK

Usually, when designing a table, we define a primary key. So when we are ready, the table is created at once with columns and a primary key. I think it is the most common case. Using a Liquibase changeset in XML format, it may look like this:

<changeSet id="Create table with PK" author="DBA presents">
  <createTable tableName="USERS">
     <column name="US_ID" type="int">
        <constraints primaryKey="true" primaryKeyName="PK_USERS" nullable="false" />
     </column>
     <column name="US_NAME" type="varchar(100)" />
  </createTable>
</changeSet>

The createTable change creates a new table with two columns. One of them (US_ID) has an additional constraint - a primary key. As you can see, the primary key is defined by a nested XML tag - constraints. This example has three elements that are most commonly used when defining a primary key:

• primaryKey - it is a flag (true or false). When set to true, the column is marked as a primary key.
• primaryKeyName - it is an optional attribute, but very useful. I highly recommend always setting it to a custom value. It will be easier to manage the constraint in the future. It is a totally new name that will be assigned to the newly created primary key.
• nullable - it is a flag (true or false). It is true by default, so if you do not want to allow null values in the column, set it to false.

As Liquibase changes support multiple database engines, there are also attributes specific to Oracle like primaryKeyTablespace, which allows to define on which tablespace an index supporting the primary key will be created.

The above XML changeset is an equivalent of this SQL statement in MySQL:

CREATE TABLE USERS (
    US_ID INT NOT NULL,
    US_NAME VARCHAR(100) NULL,
    CONSTRAINT PK_USERS PRIMARY KEY (US_ID)
);

Composite PK

It is unobvious how to deal with a primary key built on multiple columns like in the CITIES table from the beginning of the article. It is simpler than it might seem. It is just enough to add the constraints tag with the primary key attributes to all columns, we want to create the key on. For example, to create the CITIES table, the change will look like this:

<changeSet id="Create table with composite PK" author="DBA presents">
  <createTable tableName="CITIES">
     <column name="CI_COUNTRY" type="char(2)">
        <constraints primaryKey="true" primaryKeyName="PK_CITIES" nullable="false" />
     </column>
     <column name="CI_POSTAL_CODE" type="varchar(10)">
        <constraints primaryKey="true" primaryKeyName="PK_CITIES" nullable="false" />
     </column>
     <column name="CI_NAME" type="varchar(50)">
        <constraints primaryKey="true" primaryKeyName="PK_CITIES" nullable="false" />
     </column>
  </createTable>
</changeSet>

All three columns are marked as primary keys with the same primary key name. Not all of them must be not nullable, but in this example they are. Let's see how the SQL command generated by this XML change looks like:

CREATE TABLE employees.CITIES (
   CI_COUNTRY CHAR(2) NOT NULL,
   CI_POSTAL_CODE VARCHAR(10) NOT NULL,
   CI_NAME VARCHAR(50) NOT NULL,
   CONSTRAINT PK_CITIES PRIMARY KEY (CI_COUNTRY, CI_POSTAL_CODE, CI_NAME)
);

This is exactly what we wanted - a country, a postal code and a name are included in the primary key. One limitation is visible here. We cannot have a different columns order in the primary key than the order of columns in the table. It is that way, because we only mark individual columns as included in the PK. Fortunately, there is a workaround - creating a primary key separately.

Add primary key to existing table

Rarely, but still, you may want to add a primary key to a table that already exists. This option might be more convenient, when creating a composite primary key as this way you can freely define the columns order. Assume, that we already have a table CITIES, but it does not have a primary key, yet. To just add a PK, without recreating the table, we can use an addPrimaryKey change.

<changeSet id="Add PK" author="DBA presents">
  <addPrimaryKey tableName="CITIES" columnNames="CI_NAME, CI_POSTAL_CODE, CI_COUNTRY" constraintName="PK_CITIES" />
</changeSet>

The columnNames attribute can be set to a single column name or a list of column names that will be used to form a primary key. As that is a list, any column order can be provided, even if the order does not match the columns in the table. Here is the SQL generated for this changeset.

ALTER TABLE employees.CITIES
   ADD PRIMARY KEY (CI_NAME, CI_POSTAL_CODE, CI_COUNTRY);

It uses ALTER TABLE statement with ADD PRIMARY KEY to modify an existing table.

Actually, the addPrimaryKey change has additional options, which are not available when creating a PK with a table using the constrains change. Those attributes are:

• clustered - a flag (true, false) useful especially for SQL Server as that engine has clustered and nonclustered primary keys.
• forIndexName - a name of a unique index that will be used for a PK instead of creating a new one. It works for Oracle database engine.

Of course, it is better to have a finalized table structure before inserting any data into it. So even if you haven't decided to create a table with a primary key in one change, try to create a PK before you load any data to the table.

Delete primary key

Whenever you can add an item, it is good to have a possibility to delete it. A primary key is not an exception. If a table already has a primary key and you want to change it, a good option is to delete it first, then create a new one. Liquibase has a dropPrimaryKey change to delete a primary key. Here is an example.

<changeSet id="Delete PK" author="DBA presents">
  <dropPrimaryKey tableName="CITIES" />
</changeSet>

It generates this simple SQL statement, when it is run against a MySQL database.

ALTER TABLE employees.CITIES DROP PRIMARY KEY;

This is an absolute minimum set of attributes, but some database engines offer other options or even require them:

• constraintName - you can provide the name of the primary key to delete. Of course a table may have only one primary key, but it is required for some database engines. For example, Oracle needs it as dropping a primary key there is done by dropping a constraint by its name. This is a good reason to always provide a custom name when creating a constraint in a relational database.
• dropIndex - a flag (true, false), which defines if an associated index should be deleted along with the primary key or not. It has no impact on MySQL, but for Oracle, again, it does.

So far there is no Liquibase change to modify a primary key. It is understandable as relational database engines does not have such SQL statements either. To achieve a similar result, just drop the existing PK and create a new one.

Autorollback

Liquibase autorollback support needs a few words.

As all information required to drop the primary key are already provided to the addPrimaryKey change, Liquibase knows how to rollback adding the key. So autorollback is supported.

Contrary, to add a primary key, the database engine needs to know at least on which columns it should be created. That information is missing in the dropPrimaryKey change, so Liquibase cannot construct a working rollback in that case. Hence, autorollback is not supported for the dropPrimaryKey change. If you want to have a rollback option (and you should want that), you need to manually add it to the changeset.