Loading...
Validates that references between two tables are bidirectional. If table A references table B, then table B should reference table A. Common in relationship tables, friendship/follower models, and mutual association patterns.
Validates that column values exist in a reference set or lookup table. Useful for status codes, country codes, category values, and any column that should only contain predefined valid values. Supports both inline value lists and lookup table references.
Validates that all non-null values in a foreign key column exist in the referenced parent table's primary key column. Detects orphaned references that break referential integrity.
Validates that row counts or aggregated values match between related tables. For example, the number of order line items should match the item_count on the order header, or the sum of transaction amounts should match the account balance.
Validates that diagnosis pointers on claim service lines reference valid diagnosis positions (1-12) that exist on the claim header. Each service line must have at least one diagnosis pointer, and each pointer must correspond to a populated diagnosis code on the claim. Invalid pointers cause claim rejections and delayed payments.
Detects child records that have no corresponding parent record. Orphan records indicate broken referential integrity caused by parent deletions without cascading, failed ETL jobs, or race conditions. Unlike foreign-key-valid which checks FK values, this rule focuses on finding and quantifying orphaned child records for remediation.
The 835 remittance CLP01 patient control number must reference a submitted 837 claim. An 835 line that matches no claim cannot be posted.
Each CAS claim adjustment reason code (CARC) must exist in the current X12 Claim Adjustment Reason Code list. Retired/invalid CARCs are rejected.
The reaction occurrence country must be a valid ISO 3166 country code.
The symbol must exist in the CAT reportable-securities reference (symbol master).
The plant id must exist in the EIA plant registry (EIA-860).
The energy source / fuel type code must be a valid EIA fuel code.
The reporting currency must be a valid ISO 4217 code.
The facility CCN must exist in the CMS provider registry.
The risk/governing state must be a valid US state code.
The patient/incident disposition must be a valid NEMSIS eDisposition code.
The chief complaint / primary impression must be coded against the NEMSIS SNOMED CT value set.
The reporting agency id must exist in the state/national EMS agency registry.