sqlparser/ast/
table_constraints.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18//! SQL Abstract Syntax Tree (AST) types for table constraints
19
20use crate::ast::{
21    display_comma_separated, display_separated, ConstraintCharacteristics,
22    ConstraintReferenceMatchKind, Expr, Ident, IndexColumn, IndexOption, IndexType,
23    KeyOrIndexDisplay, NullsDistinctOption, ObjectName, ReferentialAction,
24};
25use crate::tokenizer::Span;
26use core::fmt;
27
28#[cfg(not(feature = "std"))]
29use alloc::{boxed::Box, vec::Vec};
30
31#[cfg(feature = "serde")]
32use serde::{Deserialize, Serialize};
33
34#[cfg(feature = "visitor")]
35use sqlparser_derive::{Visit, VisitMut};
36
37/// A table-level constraint, specified in a `CREATE TABLE` or an
38/// `ALTER TABLE ADD <constraint>` statement.
39#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
40#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
41#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
42pub enum TableConstraint {
43    /// MySQL [definition][1] for `UNIQUE` constraints statements:\
44    /// * `[CONSTRAINT [<name>]] UNIQUE <index_type_display> [<index_name>] [index_type] (<columns>) <index_options>`
45    ///
46    /// where:
47    /// * [index_type][2] is `USING {BTREE | HASH}`
48    /// * [index_options][3] is `{index_type | COMMENT 'string' | ... %currently unsupported stmts% } ...`
49    /// * [index_type_display][4] is `[INDEX | KEY]`
50    ///
51    /// [1]: https://dev.mysql.com/doc/refman/8.3/en/create-table.html
52    /// [2]: IndexType
53    /// [3]: IndexOption
54    /// [4]: KeyOrIndexDisplay
55    Unique(UniqueConstraint),
56    /// MySQL [definition][1] for `PRIMARY KEY` constraints statements:\
57    /// * `[CONSTRAINT [<name>]] PRIMARY KEY [index_name] [index_type] (<columns>) <index_options>`
58    ///
59    /// Actually the specification have no `[index_name]` but the next query will complete successfully:
60    /// ```sql
61    /// CREATE TABLE unspec_table (
62    ///   xid INT NOT NULL,
63    ///   CONSTRAINT p_name PRIMARY KEY index_name USING BTREE (xid)
64    /// );
65    /// ```
66    ///
67    /// where:
68    /// * [index_type][2] is `USING {BTREE | HASH}`
69    /// * [index_options][3] is `{index_type | COMMENT 'string' | ... %currently unsupported stmts% } ...`
70    ///
71    /// [1]: https://dev.mysql.com/doc/refman/8.3/en/create-table.html
72    /// [2]: IndexType
73    /// [3]: IndexOption
74    PrimaryKey(PrimaryKeyConstraint),
75    /// A referential integrity constraint (`[ CONSTRAINT <name> ] FOREIGN KEY (<columns>)
76    /// REFERENCES <foreign_table> (<referred_columns>)
77    /// { [ON DELETE <referential_action>] [ON UPDATE <referential_action>] |
78    ///   [ON UPDATE <referential_action>] [ON DELETE <referential_action>]
79    /// }`).
80    ForeignKey(ForeignKeyConstraint),
81    /// `[ CONSTRAINT <name> ] CHECK (<expr>) [[NOT] ENFORCED]`
82    Check(CheckConstraint),
83    /// MySQLs [index definition][1] for index creation. Not present on ANSI so, for now, the usage
84    /// is restricted to MySQL, as no other dialects that support this syntax were found.
85    ///
86    /// `{INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option]...`
87    ///
88    /// [1]: https://dev.mysql.com/doc/refman/8.0/en/create-table.html
89    Index(IndexConstraint),
90    /// MySQLs [fulltext][1] definition. Since the [`SPATIAL`][2] definition is exactly the same,
91    /// and MySQL displays both the same way, it is part of this definition as well.
92    ///
93    /// Supported syntax:
94    ///
95    /// ```markdown
96    /// {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
97    ///
98    /// key_part: col_name
99    /// ```
100    ///
101    /// [1]: https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html
102    /// [2]: https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
103    FulltextOrSpatial(FullTextOrSpatialConstraint),
104}
105
106impl From<UniqueConstraint> for TableConstraint {
107    fn from(constraint: UniqueConstraint) -> Self {
108        TableConstraint::Unique(constraint)
109    }
110}
111
112impl From<PrimaryKeyConstraint> for TableConstraint {
113    fn from(constraint: PrimaryKeyConstraint) -> Self {
114        TableConstraint::PrimaryKey(constraint)
115    }
116}
117
118impl From<ForeignKeyConstraint> for TableConstraint {
119    fn from(constraint: ForeignKeyConstraint) -> Self {
120        TableConstraint::ForeignKey(constraint)
121    }
122}
123
124impl From<CheckConstraint> for TableConstraint {
125    fn from(constraint: CheckConstraint) -> Self {
126        TableConstraint::Check(constraint)
127    }
128}
129
130impl From<IndexConstraint> for TableConstraint {
131    fn from(constraint: IndexConstraint) -> Self {
132        TableConstraint::Index(constraint)
133    }
134}
135
136impl From<FullTextOrSpatialConstraint> for TableConstraint {
137    fn from(constraint: FullTextOrSpatialConstraint) -> Self {
138        TableConstraint::FulltextOrSpatial(constraint)
139    }
140}
141
142impl fmt::Display for TableConstraint {
143    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
144        match self {
145            TableConstraint::Unique(constraint) => constraint.fmt(f),
146            TableConstraint::PrimaryKey(constraint) => constraint.fmt(f),
147            TableConstraint::ForeignKey(constraint) => constraint.fmt(f),
148            TableConstraint::Check(constraint) => constraint.fmt(f),
149            TableConstraint::Index(constraint) => constraint.fmt(f),
150            TableConstraint::FulltextOrSpatial(constraint) => constraint.fmt(f),
151        }
152    }
153}
154
155#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
156#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
157#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
158pub struct CheckConstraint {
159    pub name: Option<Ident>,
160    pub expr: Box<Expr>,
161    /// MySQL-specific syntax
162    /// <https://dev.mysql.com/doc/refman/8.4/en/create-table.html>
163    pub enforced: Option<bool>,
164}
165
166impl fmt::Display for CheckConstraint {
167    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
168        use crate::ast::ddl::display_constraint_name;
169        write!(
170            f,
171            "{}CHECK ({})",
172            display_constraint_name(&self.name),
173            self.expr
174        )?;
175        if let Some(b) = self.enforced {
176            write!(f, " {}", if b { "ENFORCED" } else { "NOT ENFORCED" })
177        } else {
178            Ok(())
179        }
180    }
181}
182
183impl crate::ast::Spanned for CheckConstraint {
184    fn span(&self) -> Span {
185        self.expr
186            .span()
187            .union_opt(&self.name.as_ref().map(|i| i.span))
188    }
189}
190
191/// A referential integrity constraint (`[ CONSTRAINT <name> ] FOREIGN KEY (<columns>)
192/// REFERENCES <foreign_table> (<referred_columns>) [ MATCH { FULL | PARTIAL | SIMPLE } ]
193/// { [ON DELETE <referential_action>] [ON UPDATE <referential_action>] |
194///   [ON UPDATE <referential_action>] [ON DELETE <referential_action>]
195/// }`).
196#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
197#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
198#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
199pub struct ForeignKeyConstraint {
200    pub name: Option<Ident>,
201    /// MySQL-specific field
202    /// <https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html>
203    pub index_name: Option<Ident>,
204    pub columns: Vec<Ident>,
205    pub foreign_table: ObjectName,
206    pub referred_columns: Vec<Ident>,
207    pub on_delete: Option<ReferentialAction>,
208    pub on_update: Option<ReferentialAction>,
209    pub match_kind: Option<ConstraintReferenceMatchKind>,
210    pub characteristics: Option<ConstraintCharacteristics>,
211}
212
213impl fmt::Display for ForeignKeyConstraint {
214    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
215        use crate::ast::ddl::{display_constraint_name, display_option_spaced};
216        write!(
217            f,
218            "{}FOREIGN KEY{} ({}) REFERENCES {}",
219            display_constraint_name(&self.name),
220            display_option_spaced(&self.index_name),
221            display_comma_separated(&self.columns),
222            self.foreign_table,
223        )?;
224        if !self.referred_columns.is_empty() {
225            write!(f, "({})", display_comma_separated(&self.referred_columns))?;
226        }
227        if let Some(match_kind) = &self.match_kind {
228            write!(f, " {match_kind}")?;
229        }
230        if let Some(action) = &self.on_delete {
231            write!(f, " ON DELETE {action}")?;
232        }
233        if let Some(action) = &self.on_update {
234            write!(f, " ON UPDATE {action}")?;
235        }
236        if let Some(characteristics) = &self.characteristics {
237            write!(f, " {characteristics}")?;
238        }
239        Ok(())
240    }
241}
242
243impl crate::ast::Spanned for ForeignKeyConstraint {
244    fn span(&self) -> Span {
245        fn union_spans<I: Iterator<Item = Span>>(iter: I) -> Span {
246            Span::union_iter(iter)
247        }
248
249        union_spans(
250            self.name
251                .iter()
252                .map(|i| i.span)
253                .chain(self.index_name.iter().map(|i| i.span))
254                .chain(self.columns.iter().map(|i| i.span))
255                .chain(core::iter::once(self.foreign_table.span()))
256                .chain(self.referred_columns.iter().map(|i| i.span))
257                .chain(self.on_delete.iter().map(|i| i.span()))
258                .chain(self.on_update.iter().map(|i| i.span()))
259                .chain(self.characteristics.iter().map(|i| i.span())),
260        )
261    }
262}
263
264/// MySQLs [fulltext][1] definition. Since the [`SPATIAL`][2] definition is exactly the same,
265/// and MySQL displays both the same way, it is part of this definition as well.
266///
267/// Supported syntax:
268///
269/// ```markdown
270/// {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
271///
272/// key_part: col_name
273/// ```
274///
275/// [1]: https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html
276/// [2]: https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
277#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
278#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
279#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
280pub struct FullTextOrSpatialConstraint {
281    /// Whether this is a `FULLTEXT` (true) or `SPATIAL` (false) definition.
282    pub fulltext: bool,
283    /// Whether the type is followed by the keyword `KEY`, `INDEX`, or no keyword at all.
284    pub index_type_display: KeyOrIndexDisplay,
285    /// Optional index name.
286    pub opt_index_name: Option<Ident>,
287    /// Referred column identifier list.
288    pub columns: Vec<IndexColumn>,
289}
290
291impl fmt::Display for FullTextOrSpatialConstraint {
292    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
293        if self.fulltext {
294            write!(f, "FULLTEXT")?;
295        } else {
296            write!(f, "SPATIAL")?;
297        }
298
299        write!(f, "{:>}", self.index_type_display)?;
300
301        if let Some(name) = &self.opt_index_name {
302            write!(f, " {name}")?;
303        }
304
305        write!(f, " ({})", display_comma_separated(&self.columns))?;
306
307        Ok(())
308    }
309}
310
311impl crate::ast::Spanned for FullTextOrSpatialConstraint {
312    fn span(&self) -> Span {
313        fn union_spans<I: Iterator<Item = Span>>(iter: I) -> Span {
314            Span::union_iter(iter)
315        }
316
317        union_spans(
318            self.opt_index_name
319                .iter()
320                .map(|i| i.span)
321                .chain(self.columns.iter().map(|i| i.span())),
322        )
323    }
324}
325
326/// MySQLs [index definition][1] for index creation. Not present on ANSI so, for now, the usage
327/// is restricted to MySQL, as no other dialects that support this syntax were found.
328///
329/// `{INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option]...`
330///
331/// [1]: https://dev.mysql.com/doc/refman/8.0/en/create-table.html
332#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
333#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
334#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
335pub struct IndexConstraint {
336    /// Whether this index starts with KEY (true) or INDEX (false), to maintain the same syntax.
337    pub display_as_key: bool,
338    /// Index name.
339    pub name: Option<Ident>,
340    /// Optional [index type][1].
341    ///
342    /// [1]: IndexType
343    pub index_type: Option<IndexType>,
344    /// Referred column identifier list.
345    pub columns: Vec<IndexColumn>,
346    /// Optional index options such as `USING`; see [`IndexOption`].
347    pub index_options: Vec<IndexOption>,
348}
349
350impl fmt::Display for IndexConstraint {
351    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
352        write!(f, "{}", if self.display_as_key { "KEY" } else { "INDEX" })?;
353        if let Some(name) = &self.name {
354            write!(f, " {name}")?;
355        }
356        if let Some(index_type) = &self.index_type {
357            write!(f, " USING {index_type}")?;
358        }
359        write!(f, " ({})", display_comma_separated(&self.columns))?;
360        if !self.index_options.is_empty() {
361            write!(f, " {}", display_comma_separated(&self.index_options))?;
362        }
363        Ok(())
364    }
365}
366
367impl crate::ast::Spanned for IndexConstraint {
368    fn span(&self) -> Span {
369        fn union_spans<I: Iterator<Item = Span>>(iter: I) -> Span {
370            Span::union_iter(iter)
371        }
372
373        union_spans(
374            self.name
375                .iter()
376                .map(|i| i.span)
377                .chain(self.columns.iter().map(|i| i.span())),
378        )
379    }
380}
381
382/// MySQL [definition][1] for `PRIMARY KEY` constraints statements:
383/// * `[CONSTRAINT [<name>]] PRIMARY KEY [index_name] [index_type] (<columns>) <index_options>`
384///
385/// Actually the specification have no `[index_name]` but the next query will complete successfully:
386/// ```sql
387/// CREATE TABLE unspec_table (
388///   xid INT NOT NULL,
389///   CONSTRAINT p_name PRIMARY KEY index_name USING BTREE (xid)
390/// );
391/// ```
392///
393/// where:
394/// * [index_type][2] is `USING {BTREE | HASH}`
395/// * [index_options][3] is `{index_type | COMMENT 'string' | ... %currently unsupported stmts% } ...`
396///
397/// [1]: https://dev.mysql.com/doc/refman/8.3/en/create-table.html
398/// [2]: IndexType
399/// [3]: IndexOption
400#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
401#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
402#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
403pub struct PrimaryKeyConstraint {
404    /// Constraint name.
405    ///
406    /// Can be not the same as `index_name`
407    pub name: Option<Ident>,
408    /// Index name
409    pub index_name: Option<Ident>,
410    /// Optional `USING` of [index type][1] statement before columns.
411    ///
412    /// [1]: IndexType
413    pub index_type: Option<IndexType>,
414    /// Identifiers of the columns that form the primary key.
415    pub columns: Vec<IndexColumn>,
416    pub index_options: Vec<IndexOption>,
417    pub characteristics: Option<ConstraintCharacteristics>,
418}
419
420impl fmt::Display for PrimaryKeyConstraint {
421    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
422        use crate::ast::ddl::{display_constraint_name, display_option, display_option_spaced};
423        write!(
424            f,
425            "{}PRIMARY KEY{}{} ({})",
426            display_constraint_name(&self.name),
427            display_option_spaced(&self.index_name),
428            display_option(" USING ", "", &self.index_type),
429            display_comma_separated(&self.columns),
430        )?;
431
432        if !self.index_options.is_empty() {
433            write!(f, " {}", display_separated(&self.index_options, " "))?;
434        }
435
436        write!(f, "{}", display_option_spaced(&self.characteristics))?;
437        Ok(())
438    }
439}
440
441impl crate::ast::Spanned for PrimaryKeyConstraint {
442    fn span(&self) -> Span {
443        fn union_spans<I: Iterator<Item = Span>>(iter: I) -> Span {
444            Span::union_iter(iter)
445        }
446
447        union_spans(
448            self.name
449                .iter()
450                .map(|i| i.span)
451                .chain(self.index_name.iter().map(|i| i.span))
452                .chain(self.columns.iter().map(|i| i.span()))
453                .chain(self.characteristics.iter().map(|i| i.span())),
454        )
455    }
456}
457
458#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
459#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
460#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
461pub struct UniqueConstraint {
462    /// Constraint name.
463    ///
464    /// Can be not the same as `index_name`
465    pub name: Option<Ident>,
466    /// Index name
467    pub index_name: Option<Ident>,
468    /// Whether the type is followed by the keyword `KEY`, `INDEX`, or no keyword at all.
469    pub index_type_display: KeyOrIndexDisplay,
470    /// Optional `USING` of [index type][1] statement before columns.
471    ///
472    /// [1]: IndexType
473    pub index_type: Option<IndexType>,
474    /// Identifiers of the columns that are unique.
475    pub columns: Vec<IndexColumn>,
476    pub index_options: Vec<IndexOption>,
477    pub characteristics: Option<ConstraintCharacteristics>,
478    /// Optional Postgres nulls handling: `[ NULLS [ NOT ] DISTINCT ]`
479    pub nulls_distinct: NullsDistinctOption,
480}
481
482impl fmt::Display for UniqueConstraint {
483    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
484        use crate::ast::ddl::{display_constraint_name, display_option, display_option_spaced};
485        write!(
486            f,
487            "{}UNIQUE{}{:>}{}{} ({})",
488            display_constraint_name(&self.name),
489            self.nulls_distinct,
490            self.index_type_display,
491            display_option_spaced(&self.index_name),
492            display_option(" USING ", "", &self.index_type),
493            display_comma_separated(&self.columns),
494        )?;
495
496        if !self.index_options.is_empty() {
497            write!(f, " {}", display_separated(&self.index_options, " "))?;
498        }
499
500        write!(f, "{}", display_option_spaced(&self.characteristics))?;
501        Ok(())
502    }
503}
504
505impl crate::ast::Spanned for UniqueConstraint {
506    fn span(&self) -> Span {
507        fn union_spans<I: Iterator<Item = Span>>(iter: I) -> Span {
508            Span::union_iter(iter)
509        }
510
511        union_spans(
512            self.name
513                .iter()
514                .map(|i| i.span)
515                .chain(self.index_name.iter().map(|i| i.span))
516                .chain(self.columns.iter().map(|i| i.span()))
517                .chain(self.characteristics.iter().map(|i| i.span())),
518        )
519    }
520}
sqlparser/ast/table_constraints.rs

sqlparser/ast/
table_constraints.rs
