Background​

The Advantage Database Server (ADS), commonly used with Xbase++, has long served developers with its robust ISAM capabilities and data dictionary features. However, when it comes to enforcing data integrity rules, ADS has some notable limitations—it does not support constraints such as CHECK conditions or separate UNIQUE constraints like modern SQL engines such as PostgreSQL do.

The ADS Constraint Workaround​

Due to the lack of support for UNIQUE and CHECK constraints, developers working with ADS often resort to declaring any uniqueness requirement as a primary key. This has become a de facto standard for emulating uniqueness and basic data integrity in the ADS environment.

For instance, fields that are merely required to be unique (e.g., email addresses or user names) are often incorrectly defined as primary keys—even though they don't semantically represent the unique identity of a table record.

Implications During Migration​

When migrating an Xbase++ application from ADS to PostgreSQL using the ISAM emulation layer, this practice can introduce problems. PostgreSQL enforces stricter relational rules and makes a clear distinction between:
  • PRIMARY KEY – the main identifier of a row, unique and not null
  • UNIQUE constraints – to enforce uniqueness on a column or combination of columns
  • CHECK constraints – to validate data values according to custom logic
Thus, primary key misuse in the ADS dictionary can lead to incorrect schema interpretation, redundant or conflicting keys, and even migration errors.

Best Practice for Migration​

Before migrating your application to PostgreSQL using Xbase++'s ISAM emulation, it is strongly recommended to review your ADS data dictionary:
  1. Audit Primary Keys – Confirm whether each primary key truly represents the record’s identity.
  2. Reclassify Constraints – Convert pseudo-primary keys (used only for uniqueness) to proper UNIQUE constraints in PostgreSQL.
  3. Introduce CHECK Logic – Where applicable, migrate logical data validations to PostgreSQL CHECK constraints or triggers.
We recommend to upsize your data model and applying the new constraints programatically as illustrated below:

Xbase++: 
TEXT TO cSql
   ALTER TABLE users
   ADD CONSTRAINT uq_username UNIQUE (username);
ENDTEXT

oStmt := DacSqlStatement( oSession ):fromChar(cSql):build()

IF oStmt:execute()!=0
   ? "Constraint added successfully."
ELSE
   ? "Failed to add constraint:", oSession:getLastMessage()
ENDIF

Conclusion​

Understanding the limitations of ADS in constraint management is essential for a successful migration. By reassessing and correcting primary key definitions in your ADS schema, you lay the groundwork for a more semantically correct and robust PostgreSQL data model using the Xbase++ ISAM emulation layer.