detect-foreign-keys

name detect-foreign-keys description Identify foreign key relationships between tables using heuristics, value overlap analysis, and referential integrity checks Detecting Foreign Keys Purpose This component skill guides systematic foreign key relationship detection in relational databases. Use it when: Multiple tables exist in the database and relationships are undocumented Need to understand table relationships before joining data Validating referential integrity between tables Identifying orphaned records that reference non-existent parent records Referenced by importing-data or cleaning-data skills requiring relationship analysis Prerequisites Tables exist in database (relational database with SQL support) SQL query tool available (database CLI, IDE, or query interface) Table schemas have been examined (Phase 1 of understanding-data) Analysis workspace created Foreign Key Detection Process Create a TodoWrite checklist for the 5-phase FK detection process: Phase 1: Candidate Identification - pending Phase 2: Value Overlap Analysis - pending Phase 3: Cardinality Assessment - pending Phase 4: Referential Integrity Validation - pending Phase 5: Relationship Documentation - pending Mark each phase as you complete it. Document all findings in structured format. Phase 1: Candidate Identification Goal: Identify columns that are likely foreign keys based on naming patterns, data types, and uniqueness. Identify Candidate FK Columns by Naming Convention Common FK naming patterns: Columns ending in _id (e.g., customer_id , product_id ) Columns ending in Id (e.g., customerId , productId ) Columns named exactly id (but only in child tables) Columns starting with fk_ (e.g., fk_customer ) Columns matching another table name (e.g., customer in orders table) -- List all columns across all tables SELECT m.name as table_name, p.name as column_name, p.type as column_type FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%' AND ( p.name LIKE '%_id' OR p.name LIKE '%Id' OR p.name LIKE 'fk_%' OR p.name = 'id' ) ORDER BY m.name, p.name; Document: List of candidate FK columns per table Note naming patterns observed Flag columns that might be composite keys (multiple FK columns in same table) Identify Candidate PK Columns Primary key characteristics: Named id , [table]_id , or similar INTEGER or TEXT type Likely to be unique Often the first column in the table -- Find columns likely to be primary keys SELECT m.name as table_name, p.name as column_name, p.type as column_type, p.pk as is_primary_key FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%' AND ( p.pk = 1 -- Explicitly defined PK OR p.name = 'id' OR p.name = m.name || '_id' ) ORDER BY m.name; Document: Primary key columns per table Whether PKs are explicitly defined (pk=1) or inferred Tables without obvious primary keys Match FK Candidates to Potential Parent Tables Heuristic: A column named customer_id likely references a table named customers or customer . -- Cross-reference FK column names with table names -- (Pseudo-query - implement with string matching logic) -- For each FK candidate like 'customer_id': -- 1. Strip suffix (_id, Id) -- 2. Look for table named 'customers', 'customer', or similar -- 3. Record as potential relationship Document: FK candidate → Parent table mapping (e.g., orders.customer_id → customers.id ) Confidence level: High: Exact name match (e.g., customer_id → customer table) Medium: Plural/singular variation (e.g., customer_id → customers table) Low: Partial name match or ambiguous Phase 2: Value Overlap Analysis Goal: Validate FK candidates by checking if their values actually exist in the proposed parent table. Check Value Overlap Percentage For each candidate FK relationship identified in Phase 1: -- Calculate what percentage of FK values exist in parent table WITH fk_values AS ( SELECT DISTINCT child_fk_column as value FROM child_table WHERE child_fk_column IS NOT NULL ), pk_values AS ( SELECT DISTINCT parent_pk_column as value FROM parent_table WHERE parent_pk_column IS NOT NULL ), overlap AS ( SELECT COUNT ( * ) as matching_count FROM fk_values fk WHERE fk.value IN ( SELECT value FROM pk_values) ) SELECT ( SELECT COUNT ( * ) FROM fk_values) as total_fk_values, ( SELECT COUNT ( * ) FROM pk_values) as total_pk_values, overlap.matching_count, ROUND( 100.0 * overlap.matching_count / ( SELECT COUNT ( * ) FROM fk_values), 2 ) as match_percentage FROM overlap; Interpret match percentage: 100% match: Strong FK relationship (perfect referential integrity) 95-99% match: Likely FK with some orphaned records 80-94% match: Possible FK with significant orphans (investigate) <80% match: Unlikely to be true FK (name coincidence or wrong parent table) Document: Match percentage for each candidate relationship Count of orphaned FK values (values not in parent) Count of unused PK values (values not referenced by any FK) Identify Orphaned Records For relationships with <100% match: -- Find child records with FK values that don't exist in parent SELECT child_table.rowid, child_table.child_fk_column as orphaned_value, COUNT ( * ) OVER ( PARTITION BY child_table.child_fk_column) as occurrences FROM child_table LEFT JOIN parent_table ON child_table.child_fk_column = parent_table.parent_pk_column WHERE parent_table.parent_pk_column IS NULL AND child_table.child_fk_column IS NOT NULL LIMIT 20 ; Document: Sample orphaned values How many child records affected Whether orphaned values follow a pattern (all recent, specific category, etc.) Check Reverse Overlap (Unused Parent Records) -- Find parent records not referenced by any child SELECT parent_table.parent_pk_column as unused_pk_value, COUNT ( * ) as occurrence_count FROM parent_table LEFT JOIN child_table ON parent_table.parent_pk_column = child_table.child_fk_column WHERE child_table.child_fk_column IS NULL AND parent_table.parent_pk_column IS NOT NULL LIMIT 20 ; Document: Count of unused parent records Whether this is expected (e.g., new customers with no orders yet) Phase 3: Cardinality Assessment Goal: Determine the relationship type (one-to-one, one-to-many, many-to-many). Calculate FK → PK Cardinality How many child records per parent record? -- Average number of child records per parent SELECT COUNT ( * ) as total_child_records, COUNT ( DISTINCT child_fk_column) as distinct_fk_values, ROUND( 1.0 * COUNT ( * ) / NULLIF ( COUNT ( DISTINCT child_fk_column), 0 ), 2 ) as avg_children_per_parent, MIN (child_count) as min_children, MAX (child_count) as max_children FROM child_table CROSS JOIN ( SELECT child_fk_column as fk, COUNT ( * ) as child_count FROM child_table WHERE child_fk_column IS NOT NULL GROUP BY child_fk_column ); Interpret cardinality: avg = 1.0, max = 1: One-to-one relationship avg > 1.0: One-to-many relationship (most common) Multiple FK columns referencing same parent: Potential many-to-many via junction table Document: Relationship type (one-to-one, one-to-many) Average, min, max child records per parent Whether distribution is balanced or skewed Identify Many-to-Many Relationships Junction table characteristics: Table has 2+ foreign keys Few or no other columns besides FKs Composite primary key (both FKs together) -- Find tables with multiple FK candidates (potential junction tables) SELECT table_name, COUNT ( * ) as fk_column_count, GROUP_CONCAT(column_name, ', ' ) as fk_columns FROM ( SELECT m.name as table_name, p.name as column_name FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%' AND (p.name LIKE '%_id' OR p.name LIKE 'fk_%' ) ) GROUP BY table_name HAVING COUNT ( * ) >= 2 ORDER BY fk_column_count DESC ; Document: Junction tables identified Which two (or more) tables they connect Cardinality of the many-to-many relationship Check for Self-Referencing FKs Hierarchical data pattern: Table has FK pointing to its own PK (e.g., employee.manager_id → employee.id ) -- Find columns that might reference the same table SELECT table_name, column_name, type FROM ( SELECT m.name as table_name, p.name as column_name, p.type as type FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%' AND ( p.name LIKE 'parent_%' OR p.name LIKE 'manager_%' OR p.name LIKE '%_parent_id' ) ); Document: Self-referencing relationships Depth of hierarchy (max levels) Orphaned roots or cycles Phase 4: Referential Integrity Validation Goal: Quantify integrity violations and assess data quality impact. Calculate Integrity Violation Rate For each confirmed FK relationship: -- Comprehensive referential integrity check WITH integrity_check AS ( SELECT COUNT ( * ) as total_child_records, COUNT (child_fk_column) as non_null_fk_count, COUNT ( * ) - COUNT (child_fk_column) as null_fk_count, SUM ( CASE WHEN p.parent_pk_column IS NULL AND c.child_fk_column IS NOT NULL THEN 1 ELSE 0 END ) as orphaned_count FROM child_table c LEFT JOIN parent_table p ON c.child_fk_column = p.parent_pk_column ) SELECT total_child_records, non_null_fk_count, null_fk_count, ROUND( 100.0 * null_fk_count / total_child_records, 2 ) as null_fk_pct, orphaned_count, ROUND( 100.0 * orphaned_count / non_null_fk_count, 2 ) as orphaned_pct, non_null_fk_count - orphaned_count as valid_fk_count, ROUND( 100.0 * (non_null_fk_count - orphaned_count) / non_null_fk_count, 2 ) as integrity_pct FROM integrity_check; Document: Total child records NULL FK percentage (records with no parent reference) Orphaned FK percentage (records referencing non-existent parent) Valid FK percentage (clean referential integrity) Assess Impact of Integrity Violations Business impact depends on: How joins will be used (INNER vs LEFT) Whether orphaned records are recent (data entry lag) or old (data quality issue) Whether NULL FKs are expected (optional relationships) -- Analyze orphaned records by recency SELECT CASE WHEN date_column >= date ( 'now' , '-7 days' ) THEN 'Last 7 days' WHEN date_column >= date ( 'now' , '-30 days' ) THEN 'Last 30 days' WHEN date_column >= date ( 'now' , '-90 days' ) THEN 'Last 90 days' ELSE 'Older than 90 days' END as recency, COUNT ( * ) as orphaned_count FROM child_table c LEFT JOIN parent_table p ON c.child_fk_column = p.parent_pk_column WHERE p.parent_pk_column IS NULL AND c.child_fk_column IS NOT NULL AND c.date_column IS NOT NULL GROUP BY recency ORDER BY MIN (c.date_column); Document: Whether orphans are recent (may resolve soon) or old (permanent issue) Impact on analytical queries (e.g., "10% of orders will be excluded in INNER JOIN to customers") Validate Composite Keys If multiple columns together form a FK: -- Check integrity for composite FK WITH composite_fk_values AS ( SELECT DISTINCT child_table.fk_column1, child_table.fk_column2 FROM child_table WHERE child_table.fk_column1 IS NOT NULL AND child_table.fk_column2 IS NOT NULL ), composite_pk_values AS ( SELECT DISTINCT parent_table.pk_column1, parent_table.pk_column2 FROM parent_table ) SELECT COUNT ( * ) as total_composite_fk_values, SUM ( CASE WHEN pk.pk_column1 IS NULL THEN 1 ELSE 0 END ) as orphaned_count FROM composite_fk_values fk LEFT JOIN composite_pk_values pk ON fk.fk_column1 = pk.pk_column1 AND fk.fk_column2 = pk.pk_column2; Document: Composite key relationships identified Integrity percentage for composite keys Phase 5: Relationship Documentation Goal: Create structured documentation of all discovered relationships for use in cleaning and analysis. Create Relationship Catalog Document each confirmed relationship: ## Foreign Key Relationships ### High Confidence Relationships (>95% integrity) #### orders.customer _id → customers.id - **Relationship Type:** Many-to-one - **Child Table:** orders (1,523 rows) - **Parent Table:** customers (342 rows) - **Match Percentage:** 98.2% - **Cardinality:** Avg 4.5 orders per customer (min: 1, max: 47) - **NULL FKs:** 12 rows (0.8%) - **Orphaned FKs:** 15 rows (1.0%) - **Recommended Join:** LEFT JOIN (to preserve orphaned orders) - **Cleaning Action:** Investigate 15 orphaned orders, flag for review ### Medium Confidence Relationships (80-95% integrity) #### products.category_ id → categories.id - **Relationship Type:** Many-to-one - **Child Table:** products (856 rows) - **Parent Table:** categories (24 rows) - **Match Percentage:** 87.3% - **Cardinality:** Avg 35.7 products per category (min: 2, max: 142) - **NULL FKs:** 89 rows (10.4%) - **Orphaned FKs:** 20 rows (2.4%) - **Recommended Join:** INNER JOIN (if categorized products only needed) - **Cleaning Action:** Exclude or recategorize 20 orphaned products