DEV Community

Franz
Franz

Posted on

Uniface 10.4 + Informix: Simple Guide to Key Management 🔑

🤖 This blog post was created with the help of an AI assistant and is based on the Uniface 10.4 documentation section “Key Management on Informix”.

When you work with Uniface 10.4 on Informix, keys in your tables are very important. They control how rows are uniquely identified and how your application can find data efficiently. In this article, we look at primary keys and candidate keys in simple terms, with examples. 🙂

Primary keys vs. candidate keys

A primary key is the main key that uniquely identifies one row in a table. In Informix, Uniface creates the primary key from the primary key constraint that is defined when the table is created.

A candidate key is another key that can also uniquely identify a row. Candidate keys are created using unique constraints during table creation. In Informix, the constraint names for both primary keys and candidate keys follow the same naming rules as index names.

Simple example 

-- Conceptual example
CUSTOMER
--------
CUSTOMER_ID   (primary key constraint)
EMAIL         (unique constraint = candidate key)
NAME
CREATED_AT
Enter fullscreen mode Exit fullscreen mode

In this example:

  • CUSTOMER_ID is the primary key (one per row, cannot be duplicated).
  • EMAIL is a candidate key (defined as UNIQUE, so each email appears at most once).

Rules for keys on Informix

According to the Uniface 10.4 Informix connector rules, the following applies to primary and candidate keys:

  • Primary keys are not required by the DBMS. You can technically create a table without a primary key on Informix, and the database will still accept it.
  • Primary and candidate keys are allowed to overlap. That means they can share some or even all columns if needed.
  • Keys do not need to be contiguous in storage. The columns that form a key do not have to be next to each other in the physical record layout.

Even though a primary key is not mandatory at DBMS level, most real applications should have one. A clear primary key makes maintenance, joins, and tooling much easier.

Example: overlapping keys 

ORDERS
------
ORDER_ID      (primary key)
CUSTOMER_ID
ORDER_NUMBER  (candidate key - unique)
ORDER_DATE
Enter fullscreen mode Exit fullscreen mode

Here, ORDER_ID is the primary key. You could also define a unique constraint on (CUSTOMER_ID, ORDER_NUMBER) as a candidate key. This is allowed: the different keys may reuse the same columns where it makes sense.

Storage formats allowed for key fields

Uniface 10.4 specifies that all storage formats are allowed for primary or candidate key fields, except for the segmented types Text and Byte. These large segmented field types cannot be used as part of a primary or candidate key on Informix.

The documentation also notes that some combinations of Uniface data types and packing codes are not suitable as keys. One given example is boolean fields, which are usually not a good choice for key fields because they cannot uniquely identify rows by themselves.

Good and bad key examples 

-- ✅ Commonly used key fields
CUSTOMER_ID   numeric(10)
ORDER_ID      numeric(10)
EMAIL         char(100)
CODE          char(20)

-- ❌ Not suitable as key fields on Informix
COMMENTS      Text      -- segmented, not allowed
PROFILE_PIC   Byte      -- segmented, not allowed
IS_ACTIVE     Boolean   -- not a good candidate for uniqueness
Enter fullscreen mode Exit fullscreen mode

As a practical rule, choose key fields that are small, stable, and simple. Avoid using large text or binary data as part of a key, and avoid types that are not designed for uniqueness.

Typical problems developers run into

When building Uniface applications on Informix, developers often run into a few recurring key-related issues:

  • No primary key defined at all The DBMS allows it, but it becomes harder to prevent duplicate rows and to address a single row safely from the application.
  • Using Text or Byte fields in keys This is not supported for primary or candidate keys on Informix in Uniface 10.4, and will lead to errors at table creation time.
  • Key based on values that can change If you build your key from “business data” (like a name), changes to that data can make referential logic harder to manage.
  • Very wide composite keys A key with many columns is valid, but often slower and harder to use. A simple numeric ID is usually easier to handle and index.

Before and after design example

Less ideal design:

-- Key uses a name that may change
CUSTOMER
--------
NAME        (used as logical key)
EMAIL
ADDRESS
Enter fullscreen mode Exit fullscreen mode

Improved design:

CUSTOMER
--------
CUSTOMER_ID  (primary key)
NAME
EMAIL        (candidate key - unique)
ADDRESS
Enter fullscreen mode Exit fullscreen mode

In the improved design, CUSTOMER_ID is a stable technical key. You can still enforce uniqueness on EMAIL as a candidate key, but changes to name or email do not break the primary key itself.

How it looks from the Uniface side

In Uniface, you model entities, fields, and keys in the application model. When you generate or create tables on Informix from this model, Uniface maps these definitions to primary key and unique constraints on the database level.

  • A primary key definition in the Uniface model becomes a primary key constraint in Informix.
  • A unique key or index definition becomes a unique constraint (candidate key) in Informix.

If you define your keys cleanly in the Uniface model, Uniface and Informix can work together smoothly. This helps with performance, data integrity, and easier debugging when something goes wrong.

✍️ This article is a simplified explanation of the “Key Management on Informix” section from the Uniface 10.4 documentation, written with AI assistance to make the topic easier to understand.

Top comments (0)