Ограничения столбцов и таблиц

При определении столбцов и таблиц для них можно задать ограничения. Ограничения позволяют настроить поведение столбцов и таблиц. Ограничения столбцов указываются после типа столбца:

имя_столбца тип_столбца ограничения_столбца

Ограничения таблицы указываются после определения всех столбцов.

Рассмотрим, какие ограничения столбцов мы можем использовать.

PRIMARY KEY

Атрибут PRIMARY KEY задает первичный ключ таблицы. Первичный ключ уникально идентифицирует строку в таблице. Например:

 CREATE TABLE users
(
    id INTEGER PRIMARY KEY,
	name TEXT,
    age INTEGER
);

Здесь столбец id выступает в качестве первичного ключа, он будет уникально идентифицировать строку и его значение должно быть уникальным. То есть у нас не может быть таблице users более одной строки, где в столбце id было бы одно и то же значение.

Установка первичного ключа на уровне таблицы:

 CREATE TABLE users
(
    id INTEGER,
	name TEXT,
    age INTEGER,
	PRIMARY KEY(id)
);

Первичный ключ может быть составным. Такой ключ использовать сразу несколько столбцов, чтобы уникально идентифицировать строку в таблице. Например:

CREATE TABLE users
(
    id INTEGER,
	name TEXT,
    age INTEGER,
	PRIMARY KEY(id, name)
);

В данном случае в качестве первичного ключа выступает связка столбцов id и name. То есть в таблице users не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.

AUTOINCREMENT

Ограничение AUTOINCREMENT позволяет указать, что значение столбца будет автоматически увеличиваться при добавлении новой строки. Данное ограничение работает для столбцов, которые представляют тип INTEGER с ограничением PRIMARY KEY:

DROP TABLE users;
CREATE TABLE users
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
    age INTEGER
);

В данном случае значение столбца id каждой новой добавленной строки будет увеличиваться на единицу.

UNIQUE

Ограничение UNIQUE указывает, что столбец может хранить только уникальные значения.

CREATE TABLE users
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
    age INTEGER,
	email TEXT UNIQUE
);

В данном случае столбец email, который представляет телефон пользователя, может хранить только уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этого столбца будет совпадать.

Также мы можем определить это ограничение на уровне таблицы:

CREATE TABLE users
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
    age INTEGER,
	email TEXT,
	UNIQUE (name, email)
);

В данном случае уникальность значений устанавлена сразу для двух столбцов - name и email.

NULL и NOT NULL

По умолчанию любой столбец, если он не представляет первичный ключ, может принимать значение NULL, то есть фактически отсутствие формального значения. Но если мы хотим запретить подобное поведение и установить, что столбец обязательно должен иметь какое-либо значение, то для него следует установить ограничение NOT NULL:

CREATE TABLE users
(
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
    age INTEGER
);

В данном случае столбец name не допускает значение NULL.

DEFAULT

Ограничение DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.

CREATE TABLE users
(
    id INTEGER PRIMARY KEY,
	name TEXT,
    age INTEGER DEFAULT 18
);

Здесь столбец age в качестве значения по умолчанию имеет число 18.

CHECK

Ограничение CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст пользователей не может быть меньше 0 или больше 100:

CREATE TABLE users
(
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL CHECK(name !=''),
    age INTEGER NOT NULL CHECK(age >0 AND age < 100)
);

Кроме проверки возраста здесь также проверяется, что столбец name не может иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND. Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также CHECK можно использовать на уровне таблицы:

CREATE TABLE users
(
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
    age INTEGER NOT NULL,
	CHECK ((age >0 AND age < 100) AND (name !=''))
);

Оператор CONSTRAINT. Установка имени ограничений

С помощью оператора CONSTRAINT можно задать имя для ограничений. Они указываются после ключевого слова CONSTRAINT перед ограничениями на уровне таблицы:

CREATE TABLE users
(
    id INTEGER,
	name TEXT NOT NULL,
	email TEXT NOT NULL,
    age INTEGER NOT NULL,
	CONSTRAINT users_pk PRIMARY KEY(id),
    CONSTRAINT user_email_uq UNIQUE(email),
    CONSTRAINT user_age_chk CHECK(age >0 AND age < 100)
);

В данном случае ограничение для PRIMARY KEY называется users_pk, для UNIQUE - user_phone_uq, а для CHECK - user_age_chk. Смысл установки имен ограничений заключается в том, что впоследствии через эти имена мы сможем управлять ограничениями - удалять или изменять их.