Skip to main content

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