uqa_sql/schema/keys/
definition.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! The checks of a PRIMARY KEY or UNIQUE constraint's declaration and of its index, in the order `PostgreSQL`'s `transformIndexConstraint` and `DefineIndex` apply them.
8
9use crate::ast::{
10    ColumnDef, ColumnType, GeneratedColumnKind, PartitionSpec, TableKeyConstraint,
11    TableKeyConstraintKind,
12};
13use crate::schema::columns::POSTGRES_SYSTEM_COLUMNS;
14use crate::SQLError;
15
16/// `column "c" named in key does not exist`, for a key or included column that the relation lacks.
17pub fn missing_key_column(column: &str) -> SQLError {
18    SQLError::Routine {
19        sqlstate: "42703".into(),
20        message: format!("column \"{column}\" named in key does not exist"),
21    }
22}
23
24/// A key column named twice in one key.
25pub fn repeated_key_column(kind: TableKeyConstraintKind, column: &str) -> SQLError {
26    let constraint = match kind {
27        TableKeyConstraintKind::PrimaryKey => "primary key",
28        TableKeyConstraintKind::Unique => "unique",
29    };
30    SQLError::Routine {
31        sqlstate: "42701".into(),
32        message: format!("column \"{column}\" appears twice in {constraint} constraint"),
33    }
34}
35
36/// A second primary key, reported with the relation's own name.
37pub fn multiple_primary_keys(table: &str) -> SQLError {
38    SQLError::Routine {
39        sqlstate: "42P16".into(),
40        message: format!("multiple primary keys for table \"{table}\" are not allowed"),
41    }
42}
43
44/// The `WITHOUT OVERLAPS` column of a key must be a range or multirange column; a system column never is.
45pub fn validate_overlaps_column(column: &str, ty: Option<&ColumnType>) -> Result<(), SQLError> {
46    if matches!(ty, Some(ColumnType::Range(_) | ColumnType::Multirange(_))) {
47        return Ok(());
48    }
49    Err(SQLError::Routine {
50        sqlstate: "42804".into(),
51        message: format!(
52            "column \"{column}\" in WITHOUT OVERLAPS is not a range or multirange type"
53        ),
54    })
55}
56
57/// A `WITHOUT OVERLAPS` key compares at least one column by equality besides its period.
58pub fn validate_overlaps_key_length(key: &TableKeyConstraint) -> Result<(), SQLError> {
59    if !key.without_overlaps || key.columns.len() >= 2 {
60        return Ok(());
61    }
62    Err(SQLError::Routine {
63        sqlstate: "42601".into(),
64        message: "constraint using WITHOUT OVERLAPS needs at least two columns".into(),
65    })
66}
67
68/// What owns an index, which names the index in an attribute error.
69#[derive(Debug, Clone, Copy, PartialEq, Eq)]
70pub enum IndexOwner {
71    Key(TableKeyConstraintKind),
72    Index,
73}
74
75/// Whether `name` is a system column, which every table has beside its own columns.
76pub fn is_system_column(name: &str) -> bool {
77    POSTGRES_SYSTEM_COLUMNS.contains(&name)
78}
79
80/// `data type xid has no default operator class for access method "btree"`, which `ComputeIndexAttrs` reports for a key attribute whose type the access method cannot order.
81fn missing_operator_class(type_name: &str, access_method: &str) -> SQLError {
82    SQLError::Diagnostic {
83        sqlstate: "42704".into(),
84        message: format!(
85            "data type {type_name} has no default operator class for access method \"{access_method}\""
86        ),
87        detail: None,
88        hint: Some(
89            "You must specify an operator class for the index or define a default operator class for the data type."
90                .into(),
91        ),
92    }
93}
94
95/// A system column that is a key attribute needs an operator class of the index's access method for its type. The transaction and command identifiers of `xmin`, `xmax`, `cmin` and `cmax` have one only for a hash index, and the tuple identifier of `ctid` has one for a btree and a hash index; the object identifier of `tableoid` is ordered wherever integers are.
96pub fn resolve_system_key_attribute(name: &str, access_method: &str) -> Result<(), SQLError> {
97    let type_name = match name {
98        "xmin" | "xmax" => "xid",
99        "cmin" | "cmax" => "cid",
100        "ctid" => "tid",
101        _ => return Ok(()),
102    };
103    let ordered = match access_method {
104        "hash" => true,
105        "" | "btree" => type_name == "tid",
106        _ => false,
107    };
108    if ordered {
109        Ok(())
110    } else {
111        Err(missing_operator_class(
112            type_name,
113            if access_method.is_empty() {
114                "btree"
115            } else {
116                access_method
117            },
118        ))
119    }
120}
121
122/// `DefineIndex` builds no index on a system column or on a virtual generated column. `columns` are the relation's columns, and a name outside them is a system column, which the index's attributes have already been resolved to.
123pub fn validate_index_attribute(
124    columns: &[ColumnDef],
125    name: &str,
126    owner: IndexOwner,
127) -> Result<(), SQLError> {
128    let Some(column) = columns.iter().find(|column| column.name == name) else {
129        if is_system_column(name) {
130            return Err(system_column_index());
131        }
132        return Err(SQLError::Internal(format!(
133            "index attribute `{name}` is not a column"
134        )));
135    };
136    if column
137        .generated
138        .as_ref()
139        .is_some_and(|generated| generated.kind == GeneratedColumnKind::Virtual)
140    {
141        let message = match owner {
142            IndexOwner::Key(TableKeyConstraintKind::PrimaryKey) => {
143                "primary keys on virtual generated columns are not supported"
144            }
145            IndexOwner::Key(TableKeyConstraintKind::Unique) => {
146                "unique constraints on virtual generated columns are not supported"
147            }
148            IndexOwner::Index => "indexes on virtual generated columns are not supported",
149        };
150        return Err(SQLError::Routine {
151            sqlstate: "0A000".into(),
152            message: message.into(),
153        });
154    }
155    Ok(())
156}
157
158/// `index creation on system columns is not supported`.
159pub fn system_column_index() -> SQLError {
160    SQLError::Routine {
161        sqlstate: "0A000".into(),
162        message: "index creation on system columns is not supported".into(),
163    }
164}
165
166/// The relation a key's index is built on.
167pub struct KeyRelation<'a> {
168    /// The relation's catalog name.
169    pub table: &'a str,
170    pub columns: &'a [ColumnDef],
171    /// The relation's own partition key, when it is partitioned.
172    pub partition: Option<&'a PartitionSpec>,
173    /// The relation already has a primary key that a new one would conflict with: `index_check_primary_key` looks for one when ALTER TABLE adds the key or a partition declares it.
174    pub has_primary_key: bool,
175}
176
177/// Validate a key as `DefineIndex` does before it builds the key's index on `relation`: the index's attributes are resolved in order, key columns before included columns; then a second primary key, the partition key, and each attribute as one an index can hold.
178pub fn validate_key_definition(
179    relation: &KeyRelation<'_>,
180    key: &TableKeyConstraint,
181) -> Result<(), SQLError> {
182    let declared = |name: &str| relation.columns.iter().any(|column| column.name == name);
183    let access_method = if key.without_overlaps {
184        "gist"
185    } else {
186        "btree"
187    };
188    for name in &key.columns {
189        if declared(name) {
190            continue;
191        }
192        if !is_system_column(name) {
193            return Err(missing_key_column(name));
194        }
195        resolve_system_key_attribute(name, access_method)?;
196    }
197    if let Some(name) = key
198        .included_columns
199        .iter()
200        .find(|name| !declared(name) && !is_system_column(name))
201    {
202        return Err(missing_key_column(name));
203    }
204    if key.kind == TableKeyConstraintKind::PrimaryKey && relation.has_primary_key {
205        let local = uqa_core::RelationIdentity::from_legacy_name(relation.table)
206            .map_err(SQLError::Internal)?;
207        return Err(multiple_primary_keys(&local.name));
208    }
209    if let Some(partition) = relation.partition {
210        crate::schema::indexes::unique::validate_partitioned_key_constraint(
211            relation.table,
212            key,
213            partition,
214        )?;
215    }
216    for name in key.columns.iter().chain(&key.included_columns) {
217        validate_index_attribute(relation.columns, name, IndexOwner::Key(key.kind))?;
218    }
219    Ok(())
220}
221
222/// A partition below the relation a key is defined on. `DefineIndex` visits each partition after its parent, siblings in partition bound order.
223pub struct KeyPartition<'a> {
224    pub table: &'a str,
225    /// The partition's own partition key, when it is partitioned.
226    pub partition: Option<&'a PartitionSpec>,
227    /// The partition's keys before the new key reaches it.
228    pub keys: &'a [TableKeyConstraint],
229}
230
231/// How a partition receives a key defined on its ancestor.
232#[derive(Debug, Clone, Copy, PartialEq, Eq)]
233pub enum PartitionKeyIndex {
234    /// The partition has an equivalent key, whose index becomes the partition's index of the new key.
235    Adopted,
236    /// The partition builds a new index for the key.
237    Created,
238}
239
240/// A partition with an equivalent key adopts it. For any other partition `DefineIndex` builds a new index: a primary key must not meet the partition's own primary key, and the key must hold the partition's own partition key columns.
241pub fn define_partition_key(
242    partition: &KeyPartition<'_>,
243    key: &TableKeyConstraint,
244) -> Result<PartitionKeyIndex, SQLError> {
245    if partition
246        .keys
247        .iter()
248        .any(|existing| crate::schema::inheritance::alter::key_equivalent(existing, key))
249    {
250        return Ok(PartitionKeyIndex::Adopted);
251    }
252    if key.kind == TableKeyConstraintKind::PrimaryKey
253        && partition
254            .keys
255            .iter()
256            .any(|existing| existing.kind == TableKeyConstraintKind::PrimaryKey)
257    {
258        let local = uqa_core::RelationIdentity::from_legacy_name(partition.table)
259            .map_err(SQLError::Internal)?;
260        return Err(multiple_primary_keys(&local.name));
261    }
262    if let Some(spec) = partition.partition {
263        crate::schema::indexes::unique::validate_partitioned_key_constraint(
264            partition.table,
265            key,
266            spec,
267        )?;
268    }
269    Ok(PartitionKeyIndex::Created)
270}
271
272/// The keys one statement declares, in the order `transformIndexConstraints` builds their indexes: the primary key first, and a key whose index would repeat an earlier key's index dropped, giving its name to that key when the earlier key has none.
273pub fn index_order(mut keys: Vec<TableKeyConstraint>) -> Vec<TableKeyConstraint> {
274    let mut ordered = Vec::with_capacity(keys.len());
275    if let Some(position) = keys
276        .iter()
277        .position(|key| key.kind == TableKeyConstraintKind::PrimaryKey)
278    {
279        ordered.push(keys.remove(position));
280    }
281    for key in keys {
282        match ordered
283            .iter_mut()
284            .find(|prior: &&mut TableKeyConstraint| same_index(prior, &key))
285        {
286            Some(prior) => {
287                if prior.name.is_none() {
288                    prior.name = key.name;
289                }
290            }
291            None => ordered.push(key),
292        }
293    }
294    ordered
295}
296
297/// `transformIndexConstraints` compares the indexes that keys need rather than their kinds, so a UNIQUE key that repeats the primary key adds no index.
298fn same_index(left: &TableKeyConstraint, right: &TableKeyConstraint) -> bool {
299    left.columns == right.columns
300        && left.included_columns == right.included_columns
301        && left.nulls_not_distinct == right.nulls_not_distinct
302        && left.without_overlaps == right.without_overlaps
303}
uqa_sql/schema/keys/definition.rs

uqa_sql/schema/keys/
definition.rs
