Создание базы данных в SQL: CREATE DATABASE, таблицы и ограничения OTUS

Создание базы данных в SQL — это два разных шага: сначала командой CREATE DATABASE заводят саму базу (пустой контейнер под таблицы), а затем командой CREATE TABLE описывают схему — таблицы, столбцы, их типы и ограничения. Ниже разберу оба шага, дам таблицу типов и ограничений, приведу полный рабочий пример и покажу, что происходит при нарушении ограничений. Фокус — на проектировании схемы, а не на выборках и соединениях.

Содержание

Шаг 1. CREATE DATABASE — завести базу
Шаг 2. CREATE TABLE и типы столбцов
Шаг 3. Ограничения столбцов
Полный рабочий пример
Что будет при нарушении ограничения
Нормализация и индексы кратко
Выводы
Где применяется / связь с практикой
FAQ

Все примеры прогнаны в SQLite 3.51.0. Синтаксис CREATE TABLE и ограничения почти одинаковы во всех СУБД, различия по диалектам отмечаю отдельно.

Шаг 1. CREATE DATABASE — завести базу

В серверных СУБД (PostgreSQL, MySQL, SQL Server) база данных создается одной командой:

CREATE DATABASE shop;

CREATE — команда создания объекта, DATABASE уточняет, что создаем именно базу, shop — ее имя. После этого к базе подключаются и создают в ней таблицы. В SQL Server ту же операцию делают через SQL Server Management Studio: правый клик по узлу «Базы данных» -> «Создать базу данных».

SQLite устроен иначе: отдельной команды CREATE DATABASE в нем нет. База — это один файл на диске, он создается в момент открытия. То есть команда sqlite3 shop.db уже заводит базу. Это удобно для обучения: не нужен сервер, вся база лежит в одном файле.

Шаг 2. CREATE TABLE и типы столбцов

Таблица — это набор столбцов с именами и типами; строки таблицы называют записями. Тип столбца задает, какие значения в нем допустимы. Базовый синтаксис:

CREATE TABLE students (
    student_id INTEGER,
    full_name  TEXT,
    level      TEXT
);

Основные типы данных и их назначение (имена типов зависят от диалекта):

Категория	SQLite	Аналоги в других СУБД	Для чего
Целые числа	`INTEGER`	`INT`, `BIGINT`, `SMALLINT`	id, счетчики, количества
Дробные числа	`REAL`	`DOUBLE`, `FLOAT`	измерения, где нужна скорость
Точные дробные	`NUMERIC`	`DECIMAL(p, s)`, `NUMERIC(p, s)`	деньги, где важна точность в пределах scale
Строки	`TEXT`	`VARCHAR(n)`, `CHAR(n)`	имена, адреса, описания
Дата и время	`TEXT`/`NUMERIC`	`DATE`, `TIMESTAMP`, `DATETIME`	даты, отметки времени
Логический	`INTEGER` (0/1)	`BOOLEAN`	флаги «да/нет»

Две тонкости, которые часто путают новичков.

Для денег берут DECIMAL(p, s), а не FLOAT: DECIMAL хранит значение точно в пределах заданного числа знаков после запятой (scale), тогда как FLOAT/REAL — это двоичное приближение, где 0.1 + 0.2 может не дать ровно 0.3. При этом «точный» не значит «без округления»: результат все равно округляется до scale.

У VARCHAR(n) и CHAR(n) число n в большинстве СУБД — это лимит в символах, но в SQL Server для однобайтовых типов он считается в байтах, поэтому в UTF-8 многобайтовых символов поместится меньше n. Для юникода в SQL Server берут NVARCHAR(n).

Важная особенность SQLite: типы столбцов в нем — это подсказка (type affinity), а не строгое правило. SQLite — динамически типизированная СУБД и по умолчанию позволит записать строку в столбец INTEGER. Строгую типизацию включают через STRICT-таблицы (доступны с SQLite 3.37). В серверных СУБД типы проверяются жестко.

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

Ограничения (constraints) — это правила, которые СУБД проверяет при вставке и изменении данных. Они защищают целостность базы. Пять ключевых:

Ограничение	Что гарантирует	Типичное применение
`PRIMARY KEY`	Уникальность + запрет NULL, идентифицирует строку	Столбец id таблицы
`FOREIGN KEY`	Значение ссылается на существующую строку другой таблицы	Связь заказ -> клиент
`NOT NULL`	Значение обязательно, пустое не допускается	Имя, email при регистрации
`UNIQUE`	Все значения в столбце различны (NULL обычно разрешен)	Логин, номер паспорта
`DEFAULT`	Значение по умолчанию, если его не передали	Дата создания, статус «активен»

Несколько уточнений по границам.

PRIMARY KEY — это, по сути, NOT NULL плюс UNIQUE вместе. В большинстве СУБД под первичный ключ автоматически создается индекс. В SQLite столбец INTEGER PRIMARY KEY становится псевдонимом внутреннего rowid.

UNIQUE и PRIMARY KEY отличаются: первичный ключ в таблице один и не допускает NULL, уникальных ограничений может быть несколько. По NULL поведение расходится: в большинстве СУБД (SQLite, PostgreSQL, MySQL, Oracle) столбец с UNIQUE допускает сразу несколько строк с NULL, так как NULL не считается равным NULL; заметное исключение — SQL Server, где UNIQUE пропускает только одно значение NULL.

FOREIGN KEY в SQLite по умолчанию не проверяется: контроль внешних ключей нужно включить командой PRAGMA foreign_keys = ON в начале сессии, иначе ссылка на несуществующую строку пройдет молча.

Полный рабочий пример

Ниже законченная схема из двух связанных таблиц — ее можно скопировать и запустить в sqlite3 целиком. Студенты и их записи на курсы, между таблицами связь по внешнему ключу.

PRAGMA foreign_keys = ON;

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    full_name  TEXT    NOT NULL,
    email      TEXT    UNIQUE,
    level      TEXT    NOT NULL DEFAULT 'junior'
);

CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id    INTEGER NOT NULL,
    course        TEXT    NOT NULL,
    paid          INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

CREATE INDEX idx_enroll_student ON enrollments(student_id);

INSERT INTO students (full_name, email) VALUES ('Ivanov I. I.', 'ivanov@example.com');
INSERT INTO students (full_name, email, level) VALUES ('Petrova A. S.', 'petrova@example.com', 'middle');

INSERT INTO enrollments (student_id, course, paid) VALUES (1, 'SQL basics', 1);
INSERT INTO enrollments (student_id, course)       VALUES (2, 'Python intro');

SELECT s.full_name, s.level, e.course, e.paid
FROM students s
JOIN enrollments e ON e.student_id = s.student_id;

Первому студенту level не передавали, поэтому сработал DEFAULT 'junior'. Второй записи не передавали paid — подставился DEFAULT 0. Фактический вывод:

Ivanov I. I.|junior|SQL basics|1
Petrova A. S.|middle|Python intro|0

Что будет при нарушении ограничения

Ограничение полезно тем, что не дает записать плохие данные. Три частых случая на той же схеме — неверная операция, фактический результат SQLite и исправление.

Пропустили обязательный столбец full_name:

INSERT INTO students (email) VALUES ('no-name@example.com');

Runtime error: NOT NULL constraint failed: students.full_name (19)

Исправление — передать имя: INSERT INTO students (full_name, email) VALUES ('Sidorov S. S.', 'no-name@example.com');

Записали второй раз тот же email при ограничении UNIQUE:

INSERT INTO students (full_name, email) VALUES ('Dup', 'ivanov@example.com');

Runtime error: UNIQUE constraint failed: students.email (19)

Исправление — использовать другой email либо обновить существующую строку через UPDATE.

Сослались на несуществующего студента при включенном контроле внешних ключей:

INSERT INTO enrollments (student_id, course) VALUES (99, 'DevOps');

Runtime error: FOREIGN KEY constraint failed (19)

Исправление — сначала создать студента с нужным id, потом вставлять запись на курс. Напомню: без PRAGMA foreign_keys = ON эта вставка в SQLite прошла бы без ошибки и оставила «висячую» ссылку.

Нормализация и индексы кратко

Нормализация — это разбиение данных по таблицам так, чтобы один факт хранился в одном месте. В примере выше данные о студенте лежат в students, а его записи на курсы — в enrollments, связанные по student_id. Так имя студента не дублируется в каждой строке записи, и его не нужно править во многих местах при изменении. Это упрощенное правило; для строгого проектирования есть нормальные формы (1NF, 2NF, 3NF), но начинающему хватает принципа «не дублируй, а ссылайся».

Индекс — это вспомогательная структура, которая ускоряет поиск и соединение по столбцу, но замедляет вставку и занимает место. Индексы имеет смысл ставить на столбцы, по которым часто идет фильтрация и join, — например на внешний ключ student_id, как в примере.

Выводы

Создание базы — это два шага: CREATE DATABASE заводит базу (в SQLite — создание файла), CREATE TABLE описывает схему.
Тип столбца задает допустимые значения; для денег берут DECIMAL, а не FLOAT; в SQLite тип — подсказка, а не строгое правило.
Пять базовых ограничений: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT — они защищают целостность данных.
FOREIGN KEY в SQLite по умолчанию не проверяется, включайте PRAGMA foreign_keys = ON.
Нормализация убирает дублирование фактов, индексы ускоряют поиск, но замедляют запись.

Где применяется / связь с практикой

Проектирование схемы — базовый навык для всех, кто работает с данными: аналитику схема нужна, чтобы понимать, откуда брать поля для отчетов и как соединять таблицы, а разработчику — чтобы данные приложения были целостными. Умение читать CREATE TABLE и ограничения помогает и на собеседовании, и в первых рабочих задачах с базой.

Освойте тему на практике

Если хотите дойти от учебных примеров до реальных задач с данными — от схемы до аналитических запросов, — посмотрите программу курса «Аналитик данных» в Otus. Начать можно бесплатно: на открытых уроках Otus разбирают SQL и работу с базами на живых примерах.

FAQ

Чем DROP TABLE отличается от DELETE?
DELETE FROM t удаляет строки, но оставляет саму таблицу и ее схему; DROP TABLE t удаляет таблицу целиком вместе со структурой. Для удаления всех строк с быстрым сбросом счетчиков есть TRUNCATE (в тех СУБД, где он поддерживается).

Можно ли изменить таблицу после создания?
Да, командой ALTER TABLE — например добавить столбец. В SQLite набор операций ALTER TABLE ограничен (можно добавить или переименовать столбец, но не все виды изменений), в серверных СУБД он шире.

Обязателен ли первичный ключ в таблице?
Формально нет — таблица создастся и без него. Но на практике первичный ключ нужен почти всегда: он гарантирует, что каждую строку можно однозначно найти, и служит целью для внешних ключей из других таблиц.