No Foreign Keys: How Application-Managed Relationships Work (And Break)

Why Progress 4GL Had No Foreign Key Constraints

Progress OpenEdge 4GL was designed in an era when the application — not the database — was the authoritative place to express business rules. The 4GL program contained all the validation logic explicitly. Before inserting a contract row, the program checked that the customer existed. Before deleting a customer, the program checked for active contracts. The database was a fast, reliable store — the application was the referee.

This was not unique to Progress. Sybase, early Oracle, and Informix applications of the same era often took the same approach. FK constraints were available but rarely used in large transactional systems because the application developers did not trust them to handle complex cascading business logic correctly — and because the cost of a FK check on every INSERT/UPDATE/DELETE was measurable on the hardware of the time.

The result on DemoBank Finance's SQL Server database after migration:

The Four Sources of Orphaned Records

In every 4GL migration the team traces orphaned records back to four root causes:

1. Wrong Delete Order in Maintenance Scripts

A DBA script deletes a customer who had no active contracts — but the check missed historical closed contracts still referencing that customer ID. The contracts remain; the customer is gone. On DemoBank Finance this happened during a data-cleansing project in 2018 that removed 1,200 test/demo customer records without checking for dependent closed contracts.

2. Bulk Data Loads That Bypassed the 4GL Application

A portfolio acquisition in 2019 brought in 8,000 contracts from a smaller finance company. The migration was done via direct PROGRESS LOAD commands, bypassing all the 4GL RI checks. Several hundred contracts referenced asset IDs and customer IDs from the acquired company that were never fully reconciled to DemoBank Finance's own customer and asset tables.

3. Soft-Deleted Parents With Active Children

Both CUST_MAST and ASST_MAST have an ACTIVE_FLAG column rather than a hard delete. The 4GL treated a flagged-inactive parent as effectively deleted — but the SQL Server schema has no such implicit rule. The FK relationship between a contract and an inactive customer is technically an orphan in the business sense even though the customer row exists.

4. Progress OpenEdge Sequence Gaps and Resets

The Progress _Sequence table was reset twice during disaster recovery events in 2011 and 2015. The sequence for CONT_SCHED was inadvertently reset to a lower value, causing new schedule IDs to collide with IDs already assigned to payments in CONT_PAYMNT. The payments were never corrected — they reference schedule IDs that either do not exist or point to different contracts.

The Complete Orphan Audit — All 10 Relationships

Run these queries in a read-only reporting session during off-peak hours. On the DemoBank Finance database sizes (see Part 1 row counts) each query completes in under 30 seconds with the indexes defined in the Part 1 DDL. Save the results to a ORPHAN_AUDIT table so the migration team can track remediation progress.

-- ============================================================
-- DemoBank Finance — Complete Orphan Audit
-- Run before adding any FK constraint.
-- Save results to ORPHAN_AUDIT for remediation tracking.
-- ============================================================

CREATE TABLE dbo.ORPHAN_AUDIT (
    OA_AUDIT_ID      INT IDENTITY(1,1),
    OA_RUN_DATE      DATETIME2 NOT NULL DEFAULT GETDATE(),
    OA_RELATIONSHIP  CHAR(50)  NOT NULL,
    OA_CHILD_TABLE   CHAR(20)  NOT NULL,
    OA_CHILD_KEY_COL CHAR(30)  NOT NULL,
    OA_CHILD_KEY_VAL INT       NOT NULL,
    OA_PARENT_TABLE  CHAR(20)  NOT NULL,
    OA_PARENT_KEY_COL CHAR(30) NOT NULL,
    OA_NOTES         VARCHAR(200) NOT NULL DEFAULT ''
);
GO

-- ── 1. Contracts with no matching customer ──────────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_HDR.CH_CUST_ID -> CUST_MAST',
    'CONT_HDR', 'CH_CONT_ID', c.CH_CONT_ID,
    'CUST_MAST', 'CM_CUST_ID',
    'Missing customer: ' + CAST(c.CH_CUST_ID AS VARCHAR)
FROM  dbo.CONT_HDR c
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.CUST_MAST m WHERE m.CM_CUST_ID = c.CH_CUST_ID
);

-- ── 2. Contracts with no matching asset ─────────────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_HDR.CH_ASSET_ID -> ASST_MAST',
    'CONT_HDR', 'CH_CONT_ID', c.CH_CONT_ID,
    'ASST_MAST', 'AM_ASSET_ID',
    'Missing asset: ' + CAST(c.CH_ASSET_ID AS VARCHAR)
FROM  dbo.CONT_HDR c
WHERE c.CH_ASSET_ID > 0
  AND NOT EXISTS (
    SELECT 1 FROM dbo.ASST_MAST a WHERE a.AM_ASSET_ID = c.CH_ASSET_ID
);

-- ── 3. Contracts with invalid product type code ─────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_HDR.CH_PROD_CODE -> REF_PROD_TYPE',
    'CONT_HDR', 'CH_CONT_ID', c.CH_CONT_ID,
    'REF_PROD_TYPE', 'PT_PROD_CODE',
    'Invalid prod code: [' + RTRIM(c.CH_PROD_CODE) + ']'
FROM  dbo.CONT_HDR c
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.REF_PROD_TYPE p WHERE p.PT_PROD_CODE = c.CH_PROD_CODE
);

-- ── 4. Schedule rows with no matching contract ──────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_SCHED.CS_CONT_ID -> CONT_HDR',
    'CONT_SCHED', 'CS_SCHED_ID', s.CS_SCHED_ID,
    'CONT_HDR', 'CH_CONT_ID',
    'Missing contract: ' + CAST(s.CS_CONT_ID AS VARCHAR)
FROM  dbo.CONT_SCHED s
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.CONT_HDR c WHERE c.CH_CONT_ID = s.CS_CONT_ID
);

-- ── 5. Payments with no matching contract ───────────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_PAYMNT.CP_CONT_ID -> CONT_HDR',
    'CONT_PAYMNT', 'CP_PMT_ID', p.CP_PMT_ID,
    'CONT_HDR', 'CH_CONT_ID',
    'Missing contract: ' + CAST(p.CP_CONT_ID AS VARCHAR)
FROM  dbo.CONT_PAYMNT p
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.CONT_HDR c WHERE c.CH_CONT_ID = p.CP_CONT_ID
);

-- ── 6. Payments with no matching schedule row ───────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CONT_PAYMNT.CP_SCHED_ID -> CONT_SCHED',
    'CONT_PAYMNT', 'CP_PMT_ID', p.CP_PMT_ID,
    'CONT_SCHED', 'CS_SCHED_ID',
    'Missing schedule: ' + CAST(p.CP_SCHED_ID AS VARCHAR)
FROM  dbo.CONT_PAYMNT p
WHERE p.CP_SCHED_ID > 0
  AND NOT EXISTS (
    SELECT 1 FROM dbo.CONT_SCHED s WHERE s.CS_SCHED_ID = p.CP_SCHED_ID
);

-- ── 7. Customer addresses with no matching customer ─────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'CUST_ADDR.CA_CUST_ID -> CUST_MAST',
    'CUST_ADDR', 'CA_ADDR_ID', a.CA_ADDR_ID,
    'CUST_MAST', 'CM_CUST_ID',
    'Missing customer: ' + CAST(a.CA_CUST_ID AS VARCHAR)
FROM  dbo.CUST_ADDR a
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.CUST_MAST m WHERE m.CM_CUST_ID = a.CA_CUST_ID
);

-- ── 8. Assets with no matching customer ─────────────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'ASST_MAST.AM_CUST_ID -> CUST_MAST',
    'ASST_MAST', 'AM_ASSET_ID', a.AM_ASSET_ID,
    'CUST_MAST', 'CM_CUST_ID',
    'Missing customer: ' + CAST(a.AM_CUST_ID AS VARCHAR)
FROM  dbo.ASST_MAST a
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.CUST_MAST m WHERE m.CM_CUST_ID = a.AM_CUST_ID
);

-- ── 9. Assets with invalid asset type code ──────────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'ASST_MAST.AM_TYPE_CODE -> ASST_TYPE',
    'ASST_MAST', 'AM_ASSET_ID', a.AM_ASSET_ID,
    'ASST_TYPE', 'AT_TYPE_CODE',
    'Invalid type: [' + RTRIM(a.AM_TYPE_CODE) + ']'
FROM  dbo.ASST_MAST a
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.ASST_TYPE t WHERE t.AT_TYPE_CODE = a.AM_TYPE_CODE
);

-- ── 10. Insurance records with no matching asset ────────────
INSERT INTO dbo.ORPHAN_AUDIT
    (OA_RELATIONSHIP, OA_CHILD_TABLE, OA_CHILD_KEY_COL, OA_CHILD_KEY_VAL,
     OA_PARENT_TABLE, OA_PARENT_KEY_COL, OA_NOTES)
SELECT
    'ASST_INSUR.AI_ASSET_ID -> ASST_MAST',
    'ASST_INSUR', 'AI_INSUR_ID', i.AI_INSUR_ID,
    'ASST_MAST', 'AM_ASSET_ID',
    'Missing asset: ' + CAST(i.AI_ASSET_ID AS VARCHAR)
FROM  dbo.ASST_INSUR i
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.ASST_MAST a WHERE a.AM_ASSET_ID = i.AI_ASSET_ID
);

-- ── Summary report ──────────────────────────────────────────
SELECT
    OA_RELATIONSHIP,
    COUNT(*) AS OrphanCount
FROM  dbo.ORPHAN_AUDIT
WHERE CAST(OA_RUN_DATE AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY OA_RELATIONSHIP
ORDER BY COUNT(*) DESC;

The Four 4GL Patterns That Replaced FK Constraints

Understanding the original 4GL code patterns helps you decide where to add compensating triggers or CHECK constraints in SQL Server after FK constraints are in place.

Pattern 1: Pre-Write Existence Check

The most common pattern. Before every INSERT or UPDATE that sets a foreign key value, the 4GL program reads the parent record and aborts if it is not found.

-- 4GL equivalent in T-SQL: the check that ran before every contract INSERT
-- This is now replaced by the FK constraint itself
IF NOT EXISTS (SELECT 1 FROM dbo.CUST_MAST WHERE CM_CUST_ID = @CustID AND CM_ACTIVE_FLAG = 'Y')
BEGIN
    RAISERROR ('Customer %d not found or inactive — contract creation aborted.', 16, 1, @CustID);
    RETURN;
END

IF NOT EXISTS (SELECT 1 FROM dbo.ASST_MAST WHERE AM_ASSET_ID = @AssetID)
BEGIN
    RAISERROR ('Asset %d not found — contract creation aborted.', 16, 1, @AssetID);
    RETURN;
END

Pattern 2: Soft-Delete Guard

The 4GL "delete" was actually a flag update. Before flagging a customer as inactive, the program checked for active contracts — but it did not prevent the flag update if only closed/matured contracts existed. This is why inactive customers can have historical contract rows pointing to them, which a strict FK would reject after Phase 2 modernisation.

-- The 4GL soft-delete check, in T-SQL
-- Active contract check before deactivating a customer
IF EXISTS (
    SELECT 1 FROM dbo.CONT_HDR
    WHERE  CH_CUST_ID = @CustID
      AND  CH_STATUS  IN ('AC', 'AR', 'SU')   -- active/arrears/suspended
)
BEGIN
    RAISERROR ('Cannot deactivate customer %d — has active contracts.', 16, 1, @CustID);
    RETURN;
END

-- This allowed deactivation if only CL/MT/WO/PD contracts existed —
-- which means inactive customers CAN legitimately have historical contracts.
-- The FK must allow this: FK references CM_CUST_ID regardless of CM_ACTIVE_FLAG.

Pattern 3: Cascade in Application, Not Database

When a 4GL program needed to delete a parent with children, it explicitly deleted the children first in the correct order. This was written out as sequential FIND/DELETE loops in the 4GL code. The SQL Server equivalent is either a cascade FK or an explicit stored procedure that deletes in order.

-- The 4GL cascade delete pattern for a closed contract, in T-SQL
-- Order matters: delete children before parent
CREATE OR ALTER PROCEDURE dbo.usp_DeleteClosedContract
    @ContID INT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    -- Verify contract is in a terminal status before allowing delete
    IF NOT EXISTS (
        SELECT 1 FROM dbo.CONT_HDR
        WHERE  CH_CONT_ID = @ContID
          AND  CH_STATUS IN ('CL','MT','PD','WO')
    )
    BEGIN
        ROLLBACK;
        RAISERROR('Contract %d is not in a terminal status — delete rejected.', 16, 1, @ContID);
        RETURN;
    END

    -- Delete children deepest first
    DELETE dbo.CONT_NOTE    WHERE CN_CONT_ID   = @ContID;
    DELETE dbo.CONT_RATE_HIS WHERE CR_CONT_ID  = @ContID;
    DELETE dbo.CONT_STATUS  WHERE CT_CONT_ID   = @ContID;
    DELETE dbo.CONT_PAYMNT  WHERE CP_CONT_ID   = @ContID;
    DELETE dbo.CONT_SCHED   WHERE CS_CONT_ID   = @ContID;
    DELETE dbo.CONT_HDR     WHERE CH_CONT_ID   = @ContID;

    COMMIT;
END;
GO

Pattern 4: Lookup Table Validation Via UI Dropdown

Reference table relationships (product type codes, status codes, asset type codes) were enforced by restricting the UI to a dropdown bound to the reference table. Direct database access or batch loads bypassed this entirely — the source of the "invalid type: [BFLT]" errors found in ASST_MAST rows imported from the 2019 portfolio acquisition (the acquired company used a different asset type code scheme that was never mapped).

-- Find reference code violations (catches batch-load bypasses)
-- Useful before adding FK to reference tables

SELECT 'Invalid CH_PROD_CODE' AS Issue, CH_CONT_ID AS RowID, RTRIM(CH_PROD_CODE) AS BadValue
FROM dbo.CONT_HDR c
WHERE NOT EXISTS (SELECT 1 FROM dbo.REF_PROD_TYPE p WHERE p.PT_PROD_CODE = c.CH_PROD_CODE)

UNION ALL

SELECT 'Invalid AM_TYPE_CODE', AM_ASSET_ID, RTRIM(AM_TYPE_CODE)
FROM dbo.ASST_MAST a
WHERE NOT EXISTS (SELECT 1 FROM dbo.ASST_TYPE t WHERE t.AT_TYPE_CODE = a.AM_TYPE_CODE)

UNION ALL

SELECT 'Invalid CH_STATUS', CH_CONT_ID, RTRIM(CH_STATUS)
FROM dbo.CONT_HDR
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.REF_STATUS r
    WHERE r.RS_STATUS_CODE = CH_STATUS AND RTRIM(r.RS_DOMAIN) = 'CONTRACT'
)

ORDER BY Issue, RowID;

Remediating Orphaned Records: Four Strategies

Before adding FK constraints, every orphaned record must be resolved. The right strategy depends on the relationship and the business significance of the orphan.

-- ── Remediation example 1: restore missing customers from archive ──
-- Insert placeholder "archived customer" rows for deleted test customers
-- (these will be superseded by real archive restoration where records exist)

-- First, identify unique missing customer IDs
SELECT DISTINCT CH_CUST_ID AS MissingCustID
INTO #MissingCustomers
FROM dbo.CONT_HDR c
WHERE NOT EXISTS (SELECT 1 FROM dbo.CUST_MAST m WHERE m.CM_CUST_ID = c.CH_CUST_ID);

-- Insert minimal placeholder rows
INSERT INTO dbo.CUST_MAST
    (CM_CUST_ID, CM_CUST_TYPE, CM_COMPANY_NAME, CM_STATUS,
     CM_CREATED_DATE, CM_CREATED_BY, CM_ACTIVE_FLAG)
SELECT
    mc.MissingCustID,
    'CO',
    LEFT('ARCHIVED CUSTOMER ' + CAST(mc.MissingCustID AS CHAR(7)), 60),
    'IN',     -- inactive
    20260101, -- migration date
    'DATA_FIX           ',
    'N'       -- inactive
FROM #MissingCustomers mc;

DROP TABLE #MissingCustomers;

-- ── Remediation example 2: re-point payments with missing schedule IDs ──
-- Payments from the 2015 sequence-reset incident: set CP_SCHED_ID = 0
-- (treated as "unallocated" pending manual reconciliation)

UPDATE dbo.CONT_PAYMNT
SET    CP_SCHED_ID = 0,
       CP_NARRATIVE = LEFT('SCHED_ID_ORPHAN_FIX ' + CP_NARRATIVE, 60)
WHERE  CP_SCHED_ID > 0
  AND  NOT EXISTS (
    SELECT 1 FROM dbo.CONT_SCHED s WHERE s.CS_SCHED_ID = CP_SCHED_ID
  );

-- ── Remediation example 3: fix invalid asset type codes ──
-- Map acquired company codes to DemoBank Finance equivalents
UPDATE dbo.ASST_MAST
SET    AM_TYPE_CODE = CASE RTRIM(AM_TYPE_CODE)
    WHEN 'BFLT'  THEN 'VCAR '   -- "Business Fleet" → Passenger Car
    WHEN 'CMVEH' THEN 'VLCV '   -- "Commercial Vehicle" → Light Commercial
    WHEN 'HVYP'  THEN 'VHGV '   -- "Heavy Plant" → Heavy Goods Vehicle
    WHEN 'GENM'  THEN 'MACH '   -- "General Machinery" → Industrial Machinery
    ELSE                AM_TYPE_CODE
END
WHERE RTRIM(AM_TYPE_CODE) IN ('BFLT','CMVEH','HVYP','GENM');

GO

Adding FK Constraints: The Phased Strategy

Once all orphans are remediated, FK constraints can be added. The recommended order is: reference tables first (smallest, most stable), then customer/asset tables, then the contract domain tables deepest last. This mirrors the dependency graph — each new FK only touches tables whose parents are already clean.

-- ============================================================
-- Phase A: Reference table FKs (safe — reference tables are clean)
-- ============================================================

ALTER TABLE dbo.CONT_HDR
    ADD CONSTRAINT FK_CONT_HDR_PROD_CODE
    FOREIGN KEY (CH_PROD_CODE) REFERENCES dbo.REF_PROD_TYPE (PT_PROD_CODE);

ALTER TABLE dbo.ASST_MAST
    ADD CONSTRAINT FK_ASST_MAST_TYPE_CODE
    FOREIGN KEY (AM_TYPE_CODE) REFERENCES dbo.ASST_TYPE (AT_TYPE_CODE);

-- Verify both constraints are trusted (is_not_trusted = 0)
SELECT name, is_not_trusted, is_disabled
FROM   sys.foreign_keys
WHERE  name IN ('FK_CONT_HDR_PROD_CODE', 'FK_ASST_MAST_TYPE_CODE');

GO

-- ============================================================
-- Phase B: Customer domain FKs
-- ============================================================

ALTER TABLE dbo.CUST_ADDR
    ADD CONSTRAINT FK_CUST_ADDR_CUST_ID
    FOREIGN KEY (CA_CUST_ID) REFERENCES dbo.CUST_MAST (CM_CUST_ID);

ALTER TABLE dbo.CUST_CONTACT
    ADD CONSTRAINT FK_CUST_CONTACT_CUST_ID
    FOREIGN KEY (CC_CUST_ID) REFERENCES dbo.CUST_MAST (CM_CUST_ID);

ALTER TABLE dbo.CUST_CREDIT
    ADD CONSTRAINT FK_CUST_CREDIT_CUST_ID
    FOREIGN KEY (CX_CUST_ID) REFERENCES dbo.CUST_MAST (CM_CUST_ID);

GO

-- ============================================================
-- Phase C: Asset domain FKs
-- ============================================================

ALTER TABLE dbo.ASST_MAST
    ADD CONSTRAINT FK_ASST_MAST_CUST_ID
    FOREIGN KEY (AM_CUST_ID) REFERENCES dbo.CUST_MAST (CM_CUST_ID);

ALTER TABLE dbo.ASST_INSUR
    ADD CONSTRAINT FK_ASST_INSUR_ASSET_ID
    FOREIGN KEY (AI_ASSET_ID) REFERENCES dbo.ASST_MAST (AM_ASSET_ID);

ALTER TABLE dbo.ASST_VALUAT
    ADD CONSTRAINT FK_ASST_VALUAT_ASSET_ID
    FOREIGN KEY (AV_ASSET_ID) REFERENCES dbo.ASST_MAST (AM_ASSET_ID);

GO

-- ============================================================
-- Phase D: Contract domain FKs — most complex, last
-- ============================================================

ALTER TABLE dbo.CONT_HDR
    ADD CONSTRAINT FK_CONT_HDR_CUST_ID
    FOREIGN KEY (CH_CUST_ID)  REFERENCES dbo.CUST_MAST (CM_CUST_ID);

ALTER TABLE dbo.CONT_HDR
    ADD CONSTRAINT FK_CONT_HDR_ASSET_ID
    FOREIGN KEY (CH_ASSET_ID) REFERENCES dbo.ASST_MAST (AM_ASSET_ID);

ALTER TABLE dbo.CONT_SCHED
    ADD CONSTRAINT FK_CONT_SCHED_CONT_ID
    FOREIGN KEY (CS_CONT_ID)  REFERENCES dbo.CONT_HDR (CH_CONT_ID);

ALTER TABLE dbo.CONT_STATUS
    ADD CONSTRAINT FK_CONT_STATUS_CONT_ID
    FOREIGN KEY (CT_CONT_ID)  REFERENCES dbo.CONT_HDR (CH_CONT_ID);

ALTER TABLE dbo.CONT_RATE_HIS
    ADD CONSTRAINT FK_CONT_RATE_HIS_CONT_ID
    FOREIGN KEY (CR_CONT_ID)  REFERENCES dbo.CONT_HDR (CH_CONT_ID);

ALTER TABLE dbo.CONT_NOTE
    ADD CONSTRAINT FK_CONT_NOTE_CONT_ID
    FOREIGN KEY (CN_CONT_ID)  REFERENCES dbo.CONT_HDR (CH_CONT_ID);

-- Payment → Contract (mandatory)
ALTER TABLE dbo.CONT_PAYMNT
    ADD CONSTRAINT FK_CONT_PAYMNT_CONT_ID
    FOREIGN KEY (CP_CONT_ID)  REFERENCES dbo.CONT_HDR (CH_CONT_ID);

-- Payment → Schedule: CP_SCHED_ID = 0 means unallocated.
-- Since 0 is not a valid CS_SCHED_ID (sequences start at 10001),
-- we can only add this FK after changing the column to be nullable
-- and setting 0 values to NULL. That is a Phase 2 change.
-- For now, this FK is deferred.

GO

-- ============================================================
-- Final check: all FK constraints should be trusted
-- ============================================================
SELECT
    t.name  AS TableName,
    fk.name AS FKName,
    fk.is_disabled,
    fk.is_not_trusted,
    CASE WHEN fk.is_not_trusted = 0 THEN 'TRUSTED' ELSE 'NOT TRUSTED' END AS TrustStatus
FROM   sys.foreign_keys    fk
JOIN   sys.tables          t  ON t.object_id = fk.parent_object_id
ORDER  BY t.name, fk.name;

Maintaining RI Before FK Constraints Are Added: The Interim Trigger

During the window between migration go-live and the completion of orphan remediation, the application code provides RI. But for emergency data corrections and DBA scripts that bypass the application, an interim trigger provides a safety net on the most critical relationship: new contracts must have a valid customer.

-- Interim RI trigger: catches direct INSERT/UPDATE that bypass application checks
-- Remove this trigger once FK_CONT_HDR_CUST_ID is in place (Phase D above)
CREATE OR ALTER TRIGGER trg_CONT_HDR_RI_CustCheck
ON dbo.CONT_HDR
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(CH_CUST_ID)
    BEGIN
        IF EXISTS (
            SELECT 1 FROM inserted i
            WHERE NOT EXISTS (
                SELECT 1 FROM dbo.CUST_MAST m WHERE m.CM_CUST_ID = i.CH_CUST_ID
            )
        )
        BEGIN
            RAISERROR ('RI violation: CH_CUST_ID references a non-existent customer.', 16, 1);
            ROLLBACK TRANSACTION;
        END
    END
END;
GO

What's Next in the DemoBank Finance Series

• Part 4 — The Migration Plan: The phased cutover strategy from Progress OpenEdge to SQL Server 2019, the SSIS pipeline design for the parallel operation period, and the data quality surprises found during the first live month.
• Part 5 — Before vs After: Performance benchmarks (4-hour report batch → 3 minutes), developer productivity improvements, FCA compliance posture, and lessons learned that apply to any 4GL migration project.

The orphan audit script, remediation examples, and phased FK strategy in this article are directly reusable on any Progress 4GL, Sybase, or early Oracle system migrated to SQL Server — the patterns of missing FK constraints and accumulated orphans are consistent across all of them.

Frequently Asked Questions

Should I use WITH NOCHECK as a temporary measure?

Only if you have a specific short-term reason — for example, you need the FK defined in the schema for documentation or ORM mapping purposes but the orphan remediation is not yet complete. Set a hard deadline for completing remediation and re-enabling the constraint as trusted. An untrusted FK stays untrusted indefinitely unless you explicitly run ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ... to validate and re-trust it. Treat WITH NOCHECK as technical debt with an expiry date, not a solution.

What about cascade deletes — should I enable them?

Not on a regulated financial system. Cascade deletes are convenient but they make it possible to silently destroy large amounts of data by deleting a single parent row. On DemoBank Finance, deleting one customer could cascade-delete their contracts, schedules, and payment history — years of financial records gone without a trace. The correct approach is the stored procedure pattern shown in usp_DeleteClosedContract above: explicit, ordered, logged, and conditional on the contract being in a terminal status.

How long did the DemoBank Finance orphan remediation take?

Four weeks from the first audit run to clean data and Phase A FK constraints live. The payment-to-schedule orphans (1,203 rows from the 2015 sequence reset) took the most time — each row required a manual allocation review to confirm it was genuinely unallocated rather than incorrectly pointed at a wrong schedule. The reference code remapping (asset type codes from the 2019 acquisition) took one afternoon once the mapping table was agreed with the business.