Data quality checks in a Uniface app - orphans and bad data in SQLite without foreign keys

The database behind the customer app from the series Building a customer app in Uniface 10 has 95 tables. Not one of them declares a foreign key. The app keeps the relations consistent in its services, but that means SQLite will happily store an invoice line whose invoice no longer exists.

This article shows the Uniface service DQ3_SVC that finds such rows, and a second kind of check that looks for data that is technically valid but wrong for the business. The checks are ProcScript entries that build plain SQL, run it with sql or sql/data, and turn the result into Uniface lists and an HTML report. The SQL itself would work in any SQLite database.

Tested with Rocket Uniface 10.4.03 Community Edition (10.4.03.021) and the SQLite version of its SLE driver (3.50.2), October 2026.

Why SQLite doesn't catch it for you

Two reasons, and both are easy to miss:

1. Foreign key enforcement is off by default in SQLite. Even with REFERENCES clauses in the schema, nothing is checked unless the connection runs PRAGMA foreign_keys = ON. Every connection, every time.
2. PRAGMA foreign_key_check only knows declared keys. It is a great tool - if the schema declares the relations. Here it doesn't, so it finds nothing.

Without declared keys, the relations exist only in the code and in your head. So the first step is to write them down.

One query per relation

An orphan is a child row whose parent doesn't exist. For a single relation:

SELECT COUNT(*)
FROM SALES_LINE c
WHERE c.DOC_ID IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM SALES_DOC p WHERE p.DOC_ID = c.DOC_ID);

IS NOT NULL matters for optional relations: a sales line without a product (a free-text line) is fine, a sales line with a product ID that doesn't exist is not.

NOT EXISTS is usually the better choice over LEFT JOIN … WHERE p.DOC_ID IS NULL or NOT IN (SELECT …). NOT IN returns no rows at all if the subquery contains a NULL, which is a classic trap. NOT EXISTS has no such surprise, and SQLite can use the parent's primary key for the lookup.

In the app, one small entry runs this query for any child/parent pair and puts the result into a list keyed by TABLE.COLUMN:

entry ORPHAN
params
    string pChild : IN
    string pColumn : IN
    string pParent : IN
    string pKey : IN
    string pData : INOUT
    numeric pTotal : INOUT
endparams
variables
    numeric vN
    string vId
endvariables
    call COUNT_SQL("SELECT COUNT(*) FROM %%(pChild) c WHERE c.%%(pColumn) IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %%(pParent) p WHERE p.%%(pKey) = c.%%(pColumn))", vN)
    vId = "%%(pChild).%%(pColumn)"
    putitem/id pData, vId, vN
    if (vN > 0)
        pTotal = pTotal + vN
    endif
    return 0
end

The check itself is then just the list of relations - 48 of them for the newer half of the schema:

    call ORPHAN("SALES_DOC", "CUSTOMER_ID", "CUSTOMER", "CUSTOMER_ID", pData, pTotal)
    call ORPHAN("SALES_LINE", "DOC_ID", "SALES_DOC", "DOC_ID", pData, pTotal)
    call ORPHAN("SALES_LINE", "PRODUCT_ID", "PRODUCT", "PRODUCT_ID", pData, pTotal)
    call ORPHAN("PAYMENT", "DOC_ID", "SALES_DOC", "DOC_ID", pData, pTotal)
    call ORPHAN("DELIVERY_LINE", "DELIVERY_ID", "DELIVERY_DOC", "DELIVERY_ID", pData, pTotal)
    call ORPHAN("STOCK_MOVE", "WAREHOUSE_ID", "WAREHOUSE", "WAREHOUSE_ID", pData, pTotal)
    call ORPHAN("TICKET_COMMENT", "TICKET_ID", "TICKET", "TICKET_ID", pData, pTotal)
    call ORPHAN("JOB_RUN", "JOB_CODE", "JOB_DEF", "JOB_CODE", pData, pTotal)
    ...

The result lists every relation with its count - zeros included. A list that only shows problems looks the same whether everything is fine or the check didn't run. A list of 48 zeros is evidence.

This list is the documentation of the relations I never wrote anywhere else. If you only take one thing from this article: write the list, even if you never run it automatically.

Rows that are valid but wrong

Orphans are a structural problem. The more useful checks turned out to be business rules that no constraint could express. They all follow one pattern: one SELECT per rule, each returning the same columns plus a fixed issue code, glued together with UNION ALL:

SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_PRICE'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1 AND p.PRICE_CENTS <= 0
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_SUPPLIER'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1
   AND NOT EXISTS (SELECT 1 FROM PRODUCT_BUNDLE b WHERE b.BUNDLE_ID = p.PRODUCT_ID)
   AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_MAIN_SUPPLIER'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1
   AND EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)
   AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID AND s.IS_MAIN = 1)
UNION ALL
SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'TAX_RATE'
  FROM PRODUCT p
 WHERE p.IS_ACTIVE = 1 AND p.TAX_RATE NOT IN (0, 7, 19)
ORDER BY 1, 4;

A few details that make this pattern work:

• UNION ALL, not UNION. UNION removes duplicates, which costs a sort and can hide two different findings with identical columns.
• ORDER BY with column numbers at the very end. In a compound select it applies to the whole result, and positions avoid alias trouble.
• One product can appear several times, once per issue. That is intended: the report says what is wrong, not just that something is wrong.
• Exceptions belong in the rule. Bundles are made of other products and have no supplier of their own, so NO_SUPPLIER excludes them. Without that exception every bundle would show up as a finding - and a report full of known false alarms teaches people to ignore it.

The same pattern covers documents and other areas:

Area	Issue	Rule
Documents	OLD_DRAFT	invoice draft older than N days
	OLD_QUOTE	quote still open after its validity date
	STALE_ORDER	open order older than N days with neither an invoice nor a delivery note
	NO_LINES	issued document without a single line
Suppliers	NO_EMAIL, NO_ADDRESS, NO_PRODUCTS	active supplier missing contact data or products
Tickets	UNASSIGNED, STALE_TICKET	open ticket without owner, or not touched for N days
Purchasing	OVERDUE_PO	sent purchase order past its due date

"N days" comes from a setting with a default (30 days for drafts and orders, 14 for tickets), so the thresholds can change without touching the SQL.

How the service hands results back

Every check is a public operation with the same shape: rights check first, then a private entry that does the work, and the result as a Uniface list in an OUT parameter.

public operation PRODUCT_DQ
params
    string pData : OUT
    numeric pCount : OUT
    string pError : OUT
endparams
    pData = ""
    pCount = 0
    pError = ""
    activate "RIGHTS_SVC".REQUIRE("READ", pError)
    if ($status < 0)
        return -1
    endif
    call PRODUCT_ROWS(pData, pCount, pError)
    return $status
end

PRODUCT_ROWS builds the UNION ALL statement step by step with %%(…) substitution and runs it with sql/data:

    vNoBundle = "NOT EXISTS (SELECT 1 FROM PRODUCT_BUNDLE b WHERE b.BUNDLE_ID = p.PRODUCT_ID)"
    vSql = "SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_PRICE' FROM PRODUCT p WHERE p.IS_ACTIVE = 1 AND p.PRICE_CENTS <= 0"
    vSql = "%%(vSql) UNION ALL SELECT p.PRODUCT_ID, p.SKU, p.PRODUCT_NAME, 'NO_SUPPLIER' FROM PRODUCT p WHERE p.IS_ACTIVE = 1 AND %%(vNoBundle) AND NOT EXISTS (SELECT 1 FROM SUPPLIER_PRODUCT s WHERE s.PRODUCT_ID = p.PRODUCT_ID)"
    ...
    vSql = "%%(vSql) ORDER BY 1, 4"
    sql/data vSql, "CUSTOMERS"
    if ($status < 0)
        pError = $concat("The products could not be checked (error ", $procerror, ").")
        return -1
    endif
    pData = $result
    pCount = $itemcount(pData)

With /data, $result holds the whole result set as a Uniface list: one item per row, and each row is again a list of columns. A caller reads it with forlist and getitem - no entity, no form, no painted fields. The checks are reusable from a form, from a report and from a test in exactly the same way.

The orphan check returns a different kind of list: an associative one, built with putitem/id. The key is TABLE.COLUMN, the value the count, and a caller asks for one relation directly:

    getitem/id vDl, vData, "DELIVERY_LINE.DELIVERY_ID"

From rows to a report

All three rule groups go into one HTML file. A small entry turns any result list into a table, escaping every cell:

    forlist vRow in pData
        vLine = "<tr>"
        vI = 1
        while (vI <= pColumns)
            getitem vCell, vRow, vI
            call HTML_ESC(vCell)
            vLine = "%%(vLine)<td>%%(vCell)</td>"
            vI = vI + 1
        endwhile
        pHtml = $concat(pHtml, vLine, "</tr>", vNl)
    endfor

Escaping is not optional here. The report shows customer and supplier names, and the one with & or < in it will arrive eventually.

Testing the checks

A data quality check that never finds anything is indistinguishable from a broken one. The tests therefore create exactly one bad row per rule and assert the exact issue list per object:

    call ISSUES_OF(vData, "", "998631", vQ1)
    call ISSUES_OF(vData, "", "998632", vQ2)
    call ISSUES_OF(vData, "", "998633", vQ3)
    call CHECK("PRODUCT_DQ finds missing price, supplier, main supplier and odd tax rates", (vStatus = 0 & vQ1 = "NO_PRICE,NO_SUPPLIER" & vQ2 = "NO_MAIN_SUPPLIER,TAX_RATE" & vQ3 = ""), ...)

The third product is the important one: it is correct and must produce no finding. Without such a "clean" case, a rule that flags everything would pass.

For orphans, the test counts before, inserts one orphan delivery line and one orphan ticket comment, counts again and expects exactly two more. It also checks that the result has 48 entries, so a relation that silently drops out of the list fails the test.

Takeaways

Foreign keys in SQLite are off unless you switch them on, per connection - and foreign_key_check only knows declared keys.

Write down every relation and check it with NOT EXISTS. Report zeros too.

One SELECT per business rule, combined with UNION ALL, each with a fixed issue code.

Put the exceptions into the rule before the report teaches people to ignore it.

Test every rule with one bad and one good row.