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    validate_definition(relation, key, false)
171}
172
173/// An ALTER primary key requires the generated or original NOT NULL constraints to still exist when its index is defined.
174pub fn validate_added_key_definition(
175    relation: &KeyRelation<'_>,
176    key: &TableKeyConstraint,
177) -> Result<(), SQLError> {
178    validate_definition(relation, key, true)
179}
180
181fn validate_definition(
182    relation: &KeyRelation<'_>,
183    key: &TableKeyConstraint,
184    require_not_null: bool,
185) -> Result<(), SQLError> {
186    let declared = |name: &str| relation.columns.iter().any(|column| column.name == name);
187    let access_method = if key.without_overlaps {
188        "gist"
189    } else {
190        "btree"
191    };
192    for name in &key.columns {
193        if let Some(column) = relation.columns.iter().find(|column| column.name == *name) {
194            if access_method == "btree" {
195                require_default_btree_operator_class(&column.ty)?;
196            }
197            continue;
198        }
199        if !is_system_column(name) {
200            return Err(missing_key_column(name));
201        }
202        resolve_system_key_attribute(name, access_method)?;
203    }
204    if let Some(name) = key
205        .included_columns
206        .iter()
207        .find(|name| !declared(name) && !is_system_column(name))
208    {
209        return Err(missing_key_column(name));
210    }
211    if key.kind == TableKeyConstraintKind::PrimaryKey && relation.has_primary_key {
212        let local = uqa_core::RelationIdentity::from_legacy_name(relation.table)
213            .map_err(SQLError::Internal)?;
214        return Err(multiple_primary_keys(&local.name));
215    }
216    if require_not_null && key.kind == TableKeyConstraintKind::PrimaryKey {
217        for name in &key.columns {
218            if relation
219                .columns
220                .iter()
221                .any(|column| column.name == *name && !column.not_null)
222            {
223                return Err(SQLError::Routine {
224                    sqlstate: "42P16".into(),
225                    message: format!("primary key column \"{name}\" is not marked NOT NULL"),
226                });
227            }
228        }
229    }
230    if let Some(partition) = relation.partition {
231        crate::schema::indexes::unique::validate_partitioned_key_constraint(
232            relation.table,
233            key,
234            partition,
235        )?;
236    }
237    for name in key.columns.iter().chain(&key.included_columns) {
238        validate_index_attribute(relation.columns, name, IndexOwner::Key(key.kind))?;
239    }
240    Ok(())
241}
242
243/// A partition below the relation a key is defined on. `DefineIndex` visits each partition after its parent, siblings in partition bound order.
244pub struct KeyPartition<'a> {
245    pub table: &'a str,
246    /// The partition's own partition key, when it is partitioned.
247    pub partition: Option<&'a PartitionSpec>,
248    /// The partition's keys before the new key reaches it.
249    pub keys: &'a [TableKeyConstraint],
250}
251
252/// How a partition receives a key defined on its ancestor.
253#[derive(Debug, Clone, Copy, PartialEq, Eq)]
254pub enum PartitionKeyIndex {
255    /// The partition has an equivalent key, whose index becomes the partition's index of the new key.
256    Adopted,
257    /// The partition builds a new index for the key.
258    Created,
259}
260
261/// 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.
262pub fn define_partition_key(
263    partition: &KeyPartition<'_>,
264    key: &TableKeyConstraint,
265) -> Result<PartitionKeyIndex, SQLError> {
266    if partition
267        .keys
268        .iter()
269        .any(|existing| crate::schema::inheritance::alter::key_equivalent(existing, key))
270    {
271        return Ok(PartitionKeyIndex::Adopted);
272    }
273    if key.kind == TableKeyConstraintKind::PrimaryKey
274        && partition
275            .keys
276            .iter()
277            .any(|existing| existing.kind == TableKeyConstraintKind::PrimaryKey)
278    {
279        let local = uqa_core::RelationIdentity::from_legacy_name(partition.table)
280            .map_err(SQLError::Internal)?;
281        return Err(multiple_primary_keys(&local.name));
282    }
283    if let Some(spec) = partition.partition {
284        crate::schema::indexes::unique::validate_partitioned_key_constraint(
285            partition.table,
286            key,
287            spec,
288        )?;
289    }
290    Ok(PartitionKeyIndex::Created)
291}
292
293/// 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.
294pub fn index_order(mut keys: Vec<TableKeyConstraint>) -> Vec<TableKeyConstraint> {
295    let mut ordered = Vec::with_capacity(keys.len());
296    if let Some(position) = keys
297        .iter()
298        .position(|key| key.kind == TableKeyConstraintKind::PrimaryKey)
299    {
300        ordered.push(keys.remove(position));
301    }
302    for key in keys {
303        match ordered
304            .iter_mut()
305            .find(|prior: &&mut TableKeyConstraint| same_index(prior, &key))
306        {
307            Some(prior) => {
308                if prior.name.is_none() {
309                    prior.name = key.name;
310                }
311            }
312            None => ordered.push(key),
313        }
314    }
315    ordered
316}
317
318/// `transformIndexConstraints` compares the indexes that keys need rather than their kinds, so a UNIQUE key that repeats the primary key adds no index.
319fn same_index(left: &TableKeyConstraint, right: &TableKeyConstraint) -> bool {
320    left.columns == right.columns
321        && left.included_columns == right.included_columns
322        && left.nulls_not_distinct == right.nulls_not_distinct
323        && left.without_overlaps == right.without_overlaps
324}