CREATE TABLE: типы данных и ограничения

В этом уроке ты узнаешь

Создавать таблицу с помощью CREATE TABLE и менять её с помощью ALTER TABLE и DROP TABLE
Выбирать типы данных в разных СУБД
Защищать корректность данных с помощью ограничений

До сих пор мы работали с готовыми таблицами. Но кто и как их создаёт? Хорошая таблица — это не только список столбцов, но и набор правил: ученик без фамилии, отрицательная цена или два пользователя с одним e-mail вообще не должны попасть в базу. В этом уроке мы создадим собственные таблицы для школьных кружков.

CREATE TABLE

CREATE TABLE имя (...) перечисляет в скобках имя, тип и правила каждого столбца. Ниже создаётся таблица clubs (кружки): id — первичный ключ, name не может быть пустым и повторяться, а max_members по умолчанию равен 20 и должен быть положительным.

SQL· Измени код и запусти снова

CREATE TABLE clubs (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  room        TEXT,
  max_members INTEGER DEFAULT 20
              CONSTRAINT positive_max CHECK (max_members > 0)
);

INSERT INTO clubs (name, room) VALUES ('Chess club', '201'), ('Robotics', 'Lab 2');
INSERT INTO clubs (name, room, max_members) VALUES ('Debate', '105', 12);

SELECT * FROM clubs ORDER BY id;

▸ Ожидаемый результат

id | name | room | max_members
1 | Chess club | 201 | 20
2 | Robotics | Lab 2 | 20
3 | Debate | 105 | 12

Для первых двух кружков max_members не указан, поэтому взято значение DEFAULT — 20.

Типы данных

У каждого столбца есть тип: целое число, дробное число, текст, дата. MySQL, PostgreSQL и SQL Server строго следят за типами: в целочисленный столбец текст не запишешь. А вот названия типов в разных СУБД различаются.

Данные	SQLite	MySQL	PostgreSQL	SQL Server
Целое число	INTEGER	INT	INTEGER	INT
Точное дробное (деньги)	нет: целые копейки в INTEGER	DECIMAL(10, 2)	NUMERIC(10, 2)	DECIMAL(10, 2)
Текст	TEXT	VARCHAR(100)	VARCHAR(100), TEXT	NVARCHAR(100)
Дата	TEXT ('2025-09-15')	DATE	DATE	DATE
Истина / ложь	INTEGER (0/1)	BOOLEAN	BOOLEAN	BIT
Автономер	INTEGER PRIMARY KEY	INT AUTO_INCREMENT	INTEGER GENERATED ALWAYS AS IDENTITY	INT IDENTITY(1, 1)

VARCHAR(100) — текст длиной до 100 символов; DECIMAL(10, 2) — число из 10 цифр, 2 из которых после запятой.

А в SQLite тип — лишь рекомендация (type affinity). SQLite пытается привести значение к типу столбца: текст '15' в целочисленном столбце превращается в число 15, а 'abc' преобразовать нельзя, и он остаётся текстом. Функция TYPEOF() показывает, как значение хранится на самом деле.

SQL· Измени код и запусти снова

CREATE TABLE t (n INTEGER, s TEXT);
INSERT INTO t VALUES ('abc', 42), ('15', 7);

SELECT n, TYPEOF(n) AS n_type,
       s, TYPEOF(s) AS s_type
FROM t;

▸ Ожидаемый результат

n | n_type | s | s_type
abc | text | 42 | text
15 | integer | 7 | text

SQL· Измени код и запусти снова

SELECT 0.1 + 0.2 AS result;

▸ Ожидаемый результат

result
0.30000000000000004

Ограничения

Ограничение (constraint) — правило, которое СУБД проверяет при каждом INSERT и UPDATE. Если правило нарушено, команда отклоняется, и таблица не меняется. Ловить ошибки в самой базе, а не только в программе, надёжно: правило работает, какая бы программа ни записывала данные.

PRIMARY KEY — уникальный идентификатор строки; повторяться не может.
NOT NULL — столбец не может быть пустым (NULL).
UNIQUE — в двух строках не может быть одинакового значения (например, e-mail).
DEFAULT — значение, которое подставляется, если ничего не указано.
CHECK (условие) — условие должно выполняться, например price >= 0.
FOREIGN KEY / REFERENCES — значение должно существовать в другой таблице.

SQL· Измени код и запусти снова

CREATE TABLE clubs (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO clubs (name) VALUES ('Chess club');
INSERT INTO clubs (name) VALUES ('Chess club');  -- same name again

▸ Ожидаемый результат

Error: UNIQUE constraint failed: clubs.name

SQL· Измени код и запусти снова

CREATE TABLE clubs (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL UNIQUE,
  max_members INTEGER DEFAULT 20
              CONSTRAINT positive_max CHECK (max_members > 0)
);

INSERT INTO clubs (name, max_members) VALUES ('Drama', 0);

▸ Ожидаемый результат

Error: CHECK constraint failed: positive_max

CONSTRAINT positive_max даёт правилу имя — так его удобно узнать в сообщении об ошибке.

FOREIGN KEY: связь между таблицами

Таблица club_members хранит, какой ученик в каком кружке. REFERENCES clubs(id) говорит, что club_id должен ссылаться на существующий кружок. UNIQUE (club_id, student_id) не даёт записать ученика в один кружок дважды. Внимание: ради совместимости со старыми программами SQLite по умолчанию не проверяет внешние ключи — нужно написать PRAGMA foreign_keys = ON. MySQL (InnoDB), PostgreSQL и SQL Server проверяют их по умолчанию.

SQL· Измени код и запусти снова

PRAGMA foreign_keys = ON;

CREATE TABLE clubs (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE club_members (
  id         INTEGER PRIMARY KEY,
  club_id    INTEGER NOT NULL REFERENCES clubs(id),
  student_id INTEGER NOT NULL REFERENCES students(id),
  UNIQUE (club_id, student_id)
);

INSERT INTO clubs (name) VALUES ('Chess club'), ('Robotics');
INSERT INTO club_members (club_id, student_id) VALUES (1, 3), (1, 6), (2, 10);

SELECT c.name AS club, s.first_name
FROM club_members AS m
JOIN clubs    AS c ON c.id = m.club_id
JOIN students AS s ON s.id = m.student_id
ORDER BY c.name, s.first_name;

▸ Ожидаемый результат

club | first_name
Chess club | Leyla
Chess club | Rəşad
Robotics | Kamran

SQL· Измени код и запусти снова

PRAGMA foreign_keys = ON;

CREATE TABLE clubs (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE club_members (
  id         INTEGER PRIMARY KEY,
  club_id    INTEGER NOT NULL REFERENCES clubs(id),
  student_id INTEGER NOT NULL REFERENCES students(id)
);

INSERT INTO clubs (name) VALUES ('Chess club');
-- there is no club 5 and no student 99
INSERT INTO club_members (club_id, student_id) VALUES (5, 99);

▸ Ожидаемый результат

Error: FOREIGN KEY constraint failed

Если удалить строку с PRAGMA, SQLite молча примет эту неверную строку.

Изменение и удаление таблицы

ALTER TABLE ... ADD COLUMN добавляет в существующую таблицу новый столбец; старые строки получают в нём значение DEFAULT. Ниже мы добавляем товарам столбец скидки и даём играм скидку 15 %.

SQL· Измени код и запусти снова

ALTER TABLE products ADD COLUMN discount REAL DEFAULT 0;

UPDATE products SET discount = 0.15 WHERE category = 'Games';

SELECT name, price, discount,
       ROUND(price * (1 - discount), 2) AS final_price
FROM products
WHERE category IN ('Games', 'Home')
ORDER BY id;

▸ Ожидаемый результат

name | price | discount | final_price
Desk lamp | 34.99 | 0 | 34.99
Chess set | 42 | 0.15 | 35.7

Задание

Создай таблицу rooms для классов: id — первичный ключ, name — текст, который не может быть пустым и повторяться, seats — целое число со значением по умолчанию 30, которое должно быть больше 0. Затем добавь кабинет Lab 1 на 24 места и Hall A, не указывая число мест, и выведи name и seats, отсортировав по id.

Задание · SQL· Измени код и запусти снова

CREATE TABLE rooms (
  id INTEGER PRIMARY KEY
  -- name and seats with their constraints
);

-- insert the two rooms, then SELECT

▸ Ожидаемый результат

name | seats
Lab 1 | 24
Hall A | 30

Задание

Добавь в courses целочисленный столбец rating со значением по умолчанию 0. Поставь курсу по предмету Informatics рейтинг 5. Затем выведи название (title) и рейтинг курсов на 3 кредита, отсортировав по id.

Задание · SQL· Измени код и запусти снова

-- 1) ALTER TABLE ...
-- 2) UPDATE ...
-- 3) SELECT ...

▸ Ожидаемый результат

title | rating
Geometry | 0
Organic Chemistry | 0
Python Basics | 5

Главное

CREATE TABLE задаёт имена, типы и правила столбцов.
Названия типов в СУБД различаются; SQLite относится к типам свободно, остальные — строго.
PRIMARY KEY, NOT NULL, UNIQUE, CHECK и DEFAULT останавливают неверные данные в самой базе.
REFERENCES запрещает ссылаться на несуществующую строку; в SQLite нужен PRAGMA foreign_keys = ON.
ALTER TABLE изменяет таблицу, а DROP TABLE удаляет её навсегда.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Какое ограничение запрещает столбцу быть пустым?