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    /// Stored key carriers retain each side's declared input identity before comparison coercion.
26    pub local_types: Vec<ColumnType>,
27    pub referenced_types: Vec<ColumnType>,
28    pub exact_reference_lookup: bool,
29    /// Whether child index equality has the same operand types as the FK comparison.
30    pub exact_local_lookup: bool,
31}
32
33#[cfg(test)]
34mod tests;
35
36impl ForeignKeyComparison {
37    pub fn normalize(&self, values: Vec<Value>) -> Result<Vec<Value>, SQLError> {
38        normalize_foreign_key_values(values, &self.comparison_types)
39    }
40
41    pub fn normalize_local(&self, values: Vec<Value>) -> Result<Vec<Value>, SQLError> {
42        normalize_typed_values(values, &self.local_types, &self.comparison_types)
43    }
44
45    pub fn normalize_referenced(&self, values: Vec<Value>) -> Result<Vec<Value>, SQLError> {
46        normalize_typed_values(values, &self.referenced_types, &self.comparison_types)
47    }
48}
49
50pub fn foreign_key_relation_name(table: &str) -> String {
51    uqa_core::RelationIdentity::from_legacy_name(table)
52        .map_or_else(|_| table.to_string(), |relation| relation.name)
53}
54
55pub fn foreign_key_lookup_values(
56    catalog: &dyn PartitionCatalog,
57    table: &str,
58    fk: &ForeignKey,
59    document: &Document,
60) -> Result<Option<ForeignKeyLookup>, SQLError> {
61    let comparison = foreign_key_comparison_types(catalog, table, fk)?;
62    Ok(foreign_key_values(table, fk, document, &comparison)?
63        .map(|values| ForeignKeyLookup { values, comparison }))
64}
65
66/// 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.
67pub fn foreign_key_values(
68    table: &str,
69    fk: &ForeignKey,
70    document: &Document,
71    comparison: &ForeignKeyComparison,
72) -> Result<Option<Vec<Value>>, SQLError> {
73    let local_values: Vec<Value> = fk
74        .local_columns
75        .iter()
76        .map(|c| document.get(c).cloned().unwrap_or(Value::Null))
77        .collect();
78    let null_count = local_values
79        .iter()
80        .filter(|value| matches!(value, Value::Null))
81        .count();
82    if null_count == 0 {
83        if local_values.len() != fk.ref_columns.len() {
84            return Err(SQLError::Internal(
85                "FOREIGN KEY local and referenced column counts diverged after validation".into(),
86            ));
87        }
88        return comparison.normalize_local(local_values).map(Some);
89    }
90    match fk.match_type {
91        ForeignKeyMatch::Simple => Ok(None),
92        ForeignKeyMatch::Full if null_count == local_values.len() => Ok(None),
93        ForeignKeyMatch::Full => Err(SQLError::Diagnostic {
94            sqlstate: "23503".into(),
95            message: format!(
96                "insert or update on table \"{}\" violates foreign key constraint \"{}\"",
97                foreign_key_relation_name(table),
98                fk.name.as_deref().unwrap_or("<unnamed>")
99            ),
100            detail: Some("MATCH FULL does not allow mixing of null and nonnull key values.".into()),
101            hint: None,
102        }),
103    }
104}
105
106pub fn foreign_key_comparison_types(
107    catalog: &dyn PartitionCatalog,
108    table: &str,
109    fk: &ForeignKey,
110) -> Result<ForeignKeyComparison, SQLError> {
111    if fk.local_columns.len() != fk.ref_columns.len() {
112        return Err(SQLError::Internal(
113            "FOREIGN KEY local and referenced column counts diverged after validation".into(),
114        ));
115    }
116    let local_columns = catalog
117        .try_describe_table(table)
118        .map_err(|error| dml_storage_error("FOREIGN KEY local columns", error))?
119        .ok_or_else(|| SQLError::UnknownTable(table.to_string()))?;
120    let referenced_columns = catalog
121        .try_describe_table(&fk.ref_table)
122        .map_err(|error| dml_storage_error("FOREIGN KEY referenced columns", error))?
123        .ok_or_else(|| SQLError::UnknownTable(fk.ref_table.clone()))?;
124    let mut comparison_types = Vec::with_capacity(fk.local_columns.len());
125    let mut local_types = Vec::with_capacity(fk.local_columns.len());
126    let mut referenced_types = Vec::with_capacity(fk.ref_columns.len());
127    let mut exact_reference_lookup = true;
128    let mut exact_local_lookup = true;
129    for (local_column, referenced_column) in fk.local_columns.iter().zip(&fk.ref_columns) {
130        let local_type = local_columns
131            .iter()
132            .find(|definition| definition.name == *local_column)
133            .map(|definition| &definition.ty)
134            .ok_or_else(|| SQLError::UnknownColumn(format!("{table}.{local_column}")))?;
135        let referenced_type = referenced_columns
136            .iter()
137            .find(|definition| definition.name == *referenced_column)
138            .map(|definition| &definition.ty)
139            .ok_or_else(|| {
140                SQLError::UnknownColumn(format!("{}.{referenced_column}", fk.ref_table))
141            })?;
142        let comparison_type =
143            crate::type_resolution::foreign_key_operand_type(local_type, referenced_type).map_err(|_| {
144                SQLError::Routine {
145                    sqlstate: "42804".into(),
146                    message: format!(
147                        "foreign key constraint cannot be implemented: key columns \"{local_column}\" and \"{referenced_column}\" are of incompatible types: {} and {}",
148                        local_type.sql_name(),
149                        referenced_type.sql_name()
150                    ),
151                }
152            })?;
153        exact_reference_lookup &= comparison_type == *referenced_type;
154        exact_local_lookup &= comparison_type == *local_type;
155        comparison_types.push(comparison_type);
156        local_types.push(local_type.clone());
157        referenced_types.push(referenced_type.clone());
158    }
159    Ok(ForeignKeyComparison {
160        comparison_types,
161        local_types,
162        referenced_types,
163        exact_reference_lookup,
164        exact_local_lookup,
165    })
166}
167
168pub fn foreign_key_parent_values(
169    fk: &ForeignKey,
170    document: &Document,
171    comparison: &ForeignKeyComparison,
172) -> Result<Vec<Value>, SQLError> {
173    comparison.normalize_referenced(
174        fk.ref_columns
175            .iter()
176            .map(|column| document.get(column).cloned().unwrap_or(Value::Null))
177            .collect(),
178    )
179}
180
181fn normalize_typed_values(
182    values: Vec<Value>,
183    source_types: &[ColumnType],
184    comparison_types: &[ColumnType],
185) -> Result<Vec<Value>, SQLError> {
186    if values.len() != source_types.len() || values.len() != comparison_types.len() {
187        return Err(SQLError::Internal(
188            "FOREIGN KEY value and comparison-type counts diverged after validation".into(),
189        ));
190    }
191    values
192        .into_iter()
193        .zip(source_types)
194        .zip(comparison_types)
195        .map(|((value, source), target)| {
196            crate::assignment::conversion::convert_value_to_column_type_from(
197                value,
198                target,
199                Some(source),
200            )
201        })
202        .collect()
203}
204
205pub fn normalize_foreign_key_values(
206    values: Vec<Value>,
207    comparison_types: &[ColumnType],
208) -> Result<Vec<Value>, SQLError> {
209    if values.len() != comparison_types.len() {
210        return Err(SQLError::Internal(
211            "FOREIGN KEY value and comparison-type counts diverged after validation".into(),
212        ));
213    }
214    values
215        .into_iter()
216        .zip(comparison_types)
217        .map(|(value, ty)| crate::assignment::conversion::convert_value_to_column_type(value, ty))
218        .collect()
219}
uqa_sql/semantics/foreign_keys.rs

uqa_sql/semantics/
foreign_keys.rs
