Zen

Source string Source string

76/10000 · 76
English Actions
In a column definition after the words PRIMARY KEY, as in ``CREATE TABLE t (c INTEGER PRIMARY KEY AUTOINCREMENT);``
In a PRIMARY KEY (column-list) after a column name, as in ``CREATE TABLE t (c INTEGER, PRIMARY KEY (c AUTOINCREMENT));``
When AUTOINCREMENT is specified, the column must be a primary-key column and it must be INTEGER or UNSIGNED.
Only one column in the table may be autoincrement. However, it is legal to say ``PRIMARY KEY (a, b, c AUTOINCREMENT)`` -- in that case, there are three columns in the primary key but only the third column (``c``) is AUTOINCREMENT.
As the name suggests, values in an autoincrement column are automatically incremented. That is: if a user inserts NULL in the column, then the stored value will be the smallest non-negative integer that has not already been used. This occurs because autoincrement columns are associated with :doc:`sequences </reference/reference_lua/box_schema_sequence/create_index>`.
**UNIQUE constraints**
UNIQUE constraints look like this:
UNIQUE (column_name, ...)
There is a shorthand: specifying UNIQUE in a :ref:`column definition <sql_column_def_constraint>`.
Unique constraints are similar to primary-key constraints, except that:
A table may have any number of unique keys, and unique keys are not automatically NOT NULL.
Unique columns are automatically indexed.
Unique columns are unique. That means it is illegal to have two rows with the same values in the unique-key columns.
**Example 1: one-column unique constraint**
Create an ``author`` table with the unique ``name`` column:
CREATE TABLE author (
id INTEGER PRIMARY KEY,
name STRING UNIQUE
);
On an attempt to add an author with the same name, the following error is raised:
INSERT INTO author VALUES (3, 'Leo Tolstoy');
/*
- Duplicate key exists in unique index "unique_unnamed_AUTHOR_2" in space "AUTHOR"
with old tuple - [1, "Leo Tolstoy"] and new tuple - [3, "Leo Tolstoy"]
*/
**Example 2: two-column unique constraint**
Create a ``book`` table with the unique constraint defined on two columns:
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title STRING NOT NULL,
author_id INTEGER UNIQUE,
UNIQUE (title, author_id)
);
INSERT INTO book VALUES (1, 'War and Peace', 1),
(2, 'Crime and Punishment', 2);
On an attempt to add a book with duplicated values, the following error is raised:
INSERT INTO book VALUES (3, 'War and Peace', 1);
/*
- Duplicate key exists in unique index "unique_unnamed_BOOK_2" in space "BOOK" with
old tuple - [1, "War and Peace", 1] and new tuple - [3, "War and Peace", 1]
*/
**CHECK constraints**
The CHECK constraint is used to limit the value range that a column can store. CHECK constraints look like this:
There is a shorthand: specifying CHECK in a :ref:`column definition <sql_column_def_constraint>`.
The expression may be anything that returns a BOOLEAN result = TRUE or FALSE or UNKNOWN. |br| The expression may not contain a :ref:`subquery <sql_subquery>`. |br| If the expression contains a column name, the column must exist in the table. |br| If a CHECK constraint is specified, the table must not contain rows where the expression is FALSE. (The table may contain rows where the expression is either TRUE or UNKNOWN.) |br| Constraint checking may be stopped with :ref:`ALTER TABLE ... DISABLE CHECK CONSTRAINT <sql_alter_table>` and restarted with ALTER TABLE ... ENABLE CHECK CONSTRAINT.
**Example**
Create an ``author`` table with the ``name`` column that should contain values longer than 4 characters:
CREATE TABLE author (
id INTEGER PRIMARY KEY,
name STRING,
CONSTRAINT check_name_length CHECK (CHAR_LENGTH(name) > 4)
);

Loading…

User avatar None

New source string

testing_project / Documentation: reference/reference_sql/sql_statements_and_clauses — English

CREATE TABLE author (
id INTEGER PRIMARY KEY,
name STRING UNIQUE
);
a year ago
Browse all string changes

Things to check

Multiple failing checks

Following checks are failing:
Trailing newline: Russian, Russian, Russian

Reset

Glossary

English English
No related strings found in the glossary.

String information

Flags
read-only
String age
a year ago
Last updated
a year ago
Source string age
a year ago
Translation file
string 170