Background​


Xbase++ allows seamless integration with SQL backends like PostgreSQL using the DacSession() object. When working with SQL databases, enforcing data integrity through application logic alone is not always sufficient. Native SQL constraints (such as CHECK, UNIQUE, or FOREIGN KEY) ensure that the database maintains consistent and valid data, even when the application logic fails or behaves unexpectedly.

Example Code​


Below is a code snippet that updates the totalsales field in the customer table to an invalid value (-100). It wraps the logic in a transaction and captures any database error that may occur.

Xbase++: 
USE customer VIA (oS)
GO 1

oS:beginTransaction()
bErr := Errorblock( {|o| Break(o) } )
BEGIN SEQUENCE

   DbRLock()
   field->totalsales := -100
   DbCommit()
   oS:commitTransaction()

RECOVER USING oE
   ? Var2JSON(oE)
   oS:rollbackTransaction()

END SEQUENCE

Behavior Without Constraints​


If no SQL-level constraints are defined on the totalsales column, the code runs without any error. The negative value is committed to the database, which may violate business logic or expected application behavior.

Enforcing Integrity with SQL Constraints​


To prevent invalid data, define a CHECK constraint directly in PostgreSQL:

SQL: 
ALTER TABLE customer
ADD CONSTRAINT check_positive_sales
CHECK (totalsales >= 0);


Once the constraint is active, re-running the same Xbase++ code will result in a constraint violation. PostgreSQL rejects the transaction, and no changes are applied to the table.

Observations​

  1. The transaction is not committed.
  2. The totalsales field remains unchanged in the database.
  3. The application’s field->totalsales also reflects the original value.
  4. The oE:description property contains a meaningful error message indicating the violation of the constraint.
This behavior ensures transparency and data consistency, even if the application logic attempts to introduce invalid data.

Important Note on Primary Keys​

In PostgreSQL with Xbase++ ISAM emulation, SQL primary keys are internally reserved for the __record column. Therefore, you cannot use primary keys to enforce uniqueness in application-defined columns as you can in ADS (Advantage Database Server). Note that an SQL DBMS does not allow multiple primary keys. Use SQL constraints to enforce uniqueness instead.

SQL: 
ALTER TABLE customer
ADD CONSTRAINT unique_my_column
UNIQUE (my_unique_column);

Why Constraints Are Good and Stored Procedures Are Not for Business Rules​

While using SQL constraints such as CHECK, UNIQUE, or FOREIGN KEY is a proven best practice for enforcing data integrity, relying on stored procedures to implement business rules should generally be avoided. The primary reasons are:
  1. Hidden Logic Reduces Maintainability:
    Business rules embedded in stored procedures are often hidden from application developers and can easily become "invisible logic." This makes the application harder to understand, debug, and maintain. Just think about how debugging becomes a mess? Or look into a large project and think about the location of your business logic implementation - server or client?
  2. Poor Reusability Across Contexts:
    Stored procedures tightly couple the business rules to the database layer, which makes it difficult to reuse the same rules in different application contexts (e.g., GUI, web services, reporting). This leads to duplicated logic and inconsistent behavior across layers.
  3. Limited Testing and Version Control:
    Unlike application code, stored procedures are not usually managed within standard source control workflows and automated testing pipelines. This increases the risk of unnoticed changes, regressions, or broken business logic, especially in teams practicing continuous integration.
In contrast, SQL constraints are declarative, transparent, and always enforced, regardless of which tool or user interacts with the data. This makes constraints a cleaner, more robust choice for enforcing integrity rules directly tied to the structure of the data. To consolidate your business logic for desktop and web, use an application server as a middle tier.

Conclusion​

Using SQL constraints like CHECK is a best practice when working with Xbase++ and PostgreSQL. It offloads responsibility from the application to the database, ensuring robust and transparent data integrity mechanisms. This is especially important in multi-user environments or complex business applications where client-side logic alone is not enough to guarantee correctness. It is also a great approach when adding an application developed in languages other than Xbase++, so you can share constraints.

Next Steps:
  • Review your database schema for business rules that can be enforced via constraints.
  • Implement relevant CHECK, UNIQUE, or FOREIGN KEY constraints.
  • Use ErrorBlock() and RECOVER blocks in Xbase++ to capture and handle SQL-level errors gracefully.