uqa_sql/semantics/
foreign_keys.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Foreign-key comparison types, null matching, and normalized key values.
8use crate::{
9    ast::{ForeignKey, ForeignKeyMatch},
10    semantics::partition::PartitionCatalog,
11    ColumnType, ResultRow as Document, SQLError,
12};
13use uqa_core::Value;
14fn dml_storage_error(action: &str, error: impl std::fmt::Display) -> SQLError {
15    SQLError::Internal(format!("{action} failed in storage backend: {error}"))
16}
17
18pub struct ForeignKeyLookup {
19    pub values: Vec<Value>,
20    pub comparison: ForeignKeyComparison,
21}
22
23pub struct ForeignKeyComparison {
24    pub comparison_types: Vec<ColumnType>,
25    pub exact_reference_lookup: bool,
26    /// Whether child index equality has the same operand types as the FK comparison.
27    pub exact_local_lookup: bool,
28}
29
30impl ForeignKeyComparison {
31    pub fn normalize(&self, values: Vec<Value>) -> Result<Vec<Value>, SQLError> {
32        normalize_foreign_key_values(values, &self.comparison_types)
33    }
34}
35
36pub fn foreign_key_relation_name(table: &str) -> String {
37    uqa_core::RelationIdentity::from_legacy_name(table)
38        .map_or_else(|_| table.to_string(), |relation| relation.name)
39}
40
41pub fn foreign_key_lookup_values(
42    catalog: &dyn PartitionCatalog,
43    table: &str,
44    fk: &ForeignKey,
45    document: &Document,
46) -> Result<Option<ForeignKeyLookup>, SQLError> {
47    let comparison = foreign_key_comparison_types(catalog, table, fk)?;
48    Ok(foreign_key_values(table, fk, document, &comparison)?
49        .map(|values| ForeignKeyLookup { values, comparison }))
50}
51
52/// The normalized values of the foreign key that the row of `table` holds, or `None` when NULLs exempt the row: any NULL under `MATCH SIMPLE`, all of them under `MATCH FULL`, which rejects a key that mixes NULLs with values as `RI_FKey_check` does.
53pub fn foreign_key_values(
54    table: &str,
55    fk: &ForeignKey,
56    document: &Document,
57    comparison: &ForeignKeyComparison,
58) -> Result<Option<Vec<Value>>, SQLError> {
59    let local_values: Vec<Value> = fk
60        .local_columns
61        .iter()
62        .map(|c| document.get(c).cloned().unwrap_or(Value::Null))
63        .collect();
64    let null_count = local_values
65        .iter()
66        .filter(|value| matches!(value, Value::Null))
67        .count();
68    if null_count == 0 {
69        if local_values.len() != fk.ref_columns.len() {
70            return Err(SQLError::Internal(
71                "FOREIGN KEY local and referenced column counts diverged after validation".into(),
72            ));
73        }
74        return comparison.normalize(local_values).map(Some);
75    }
76    match fk.match_type {
77        ForeignKeyMatch::Simple => Ok(None),
78        ForeignKeyMatch::Full if null_count == local_values.len() => Ok(None),
79        ForeignKeyMatch::Full => Err(SQLError::Diagnostic {
80            sqlstate: "23503".into(),
81            message: format!(
82                "insert or update on table \"{}\" violates foreign key constraint \"{}\"",
83                foreign_key_relation_name(table),
84                fk.name.as_deref().unwrap_or("<unnamed>")
85            ),
86            detail: Some("MATCH FULL does not allow mixing of null and nonnull key values.".into()),
87            hint: None,
88        }),
89    }
90}
91
92pub fn foreign_key_comparison_types(
93    catalog: &dyn PartitionCatalog,
94    table: &str,
95    fk: &ForeignKey,
96) -> Result<ForeignKeyComparison, SQLError> {
97    if fk.local_columns.len() != fk.ref_columns.len() {
98        return Err(SQLError::Internal(
99            "FOREIGN KEY local and referenced column counts diverged after validation".into(),
100        ));
101    }
102    let local_columns = catalog
103        .try_describe_table(table)
104        .map_err(|error| dml_storage_error("FOREIGN KEY local columns", error))?
105        .ok_or_else(|| SQLError::UnknownTable(table.to_string()))?;
106    let referenced_columns = catalog
107        .try_describe_table(&fk.ref_table)
108        .map_err(|error| dml_storage_error("FOREIGN KEY referenced columns", error))?
109        .ok_or_else(|| SQLError::UnknownTable(fk.ref_table.clone()))?;
110    let mut comparison_types = Vec::with_capacity(fk.local_columns.len());
111    let mut exact_reference_lookup = true;
112    let mut exact_local_lookup = true;
113    for (local_column, referenced_column) in fk.local_columns.iter().zip(&fk.ref_columns) {
114        let local_type = local_columns
115            .iter()
116            .find(|definition| definition.name == *local_column)
117            .map(|definition| &definition.ty)
118            .ok_or_else(|| SQLError::UnknownColumn(format!("{table}.{local_column}")))?;
119        let referenced_type = referenced_columns
120            .iter()
121            .find(|definition| definition.name == *referenced_column)
122            .map(|definition| &definition.ty)
123            .ok_or_else(|| {
124                SQLError::UnknownColumn(format!("{}.{referenced_column}", fk.ref_table))
125            })?;
126        let comparison_type =
127            crate::type_resolution::foreign_key_operand_type(local_type, referenced_type).map_err(|_| {
128                SQLError::Routine {
129                    sqlstate: "42804".into(),
130                    message: format!(
131                        "foreign key constraint cannot be implemented: key columns \"{local_column}\" and \"{referenced_column}\" are of incompatible types: {} and {}",
132                        local_type.sql_name(),
133                        referenced_type.sql_name()
134                    ),
135                }
136            })?;
137        exact_reference_lookup &= comparison_type == *referenced_type;
138        exact_local_lookup &= comparison_type == *local_type;
139        comparison_types.push(comparison_type);
140    }
141    Ok(ForeignKeyComparison {
142        comparison_types,
143        exact_reference_lookup,
144        exact_local_lookup,
145    })
146}
147
148pub fn foreign_key_parent_values(
149    fk: &ForeignKey,
150    document: &Document,
151    comparison: &ForeignKeyComparison,
152) -> Result<Vec<Value>, SQLError> {
153    comparison.normalize(
154        fk.ref_columns
155            .iter()
156            .map(|column| document.get(column).cloned().unwrap_or(Value::Null))
157            .collect(),
158    )
159}
160
161pub fn normalize_foreign_key_values(
162    values: Vec<Value>,
163    comparison_types: &[ColumnType],
164) -> Result<Vec<Value>, SQLError> {
165    if values.len() != comparison_types.len() {
166        return Err(SQLError::Internal(
167            "FOREIGN KEY value and comparison-type counts diverged after validation".into(),
168        ));
169    }
170    values
171        .into_iter()
172        .zip(comparison_types)
173        .map(|(value, ty)| crate::assignment::conversion::convert_value_to_column_type(value, ty))
174        .collect()
175}
uqa_sql/semantics/foreign_keys.rs

uqa_sql/semantics/
foreign_keys.rs
