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

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

Сразу договоримся о среде: принципы проектирования схемы универсальны, а конкретные команды, типы и ограничения зависят от СУБД. Рабочий пример ниже я прогоняю в SQLite 3.51.0 — это удобная учебная среда: не нужен сервер, вся база лежит в одном файле. Там, где SQLite ведет себя иначе, чем серверные PostgreSQL, MySQL и SQL Server, я отмечаю это отдельно, а в конце привожу таблицу переноса схемы между ними.

Шаг 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
);

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

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

Для денег в серверных СУБД (PostgreSQL, MySQL, SQL Server) берут DECIMAL(p, s)/NUMERIC(p, s), а не FLOAT: там DECIMAL хранит значение точно в пределах заданного числа знаков после запятой (scale), тогда как FLOAT — это двоичное приближение, где 0.1 + 0.2 может не дать ровно 0.3. При этом «точный» не значит «без округления»: результат все равно округляется до scale. Важная оговорка про наш учебный SQLite: здесь DECIMAL(10, 5) задает лишь NUMERIC affinity, а не настоящий fixed-point, и дробное значение может лечь на диск как REAL — то самое двоичное приближение IEEE 754 (это легко увидеть: typeof у сохраненного 0.1 вернет real). Поэтому в SQLite для строгой денежной точности храните сумму целым числом минимальных единиц (копейки, центы) либо контролируйте округление на стороне приложения.

У VARCHAR(n) и CHAR(n) число n в большинстве СУБД — это лимит в символах, но в SQL Server для однобайтовых типов он считается в байтах, поэтому в UTF-8 многобайтовых символов поместится меньше n. Для юникода в SQL Server берут NVARCHAR(n). А вот в SQLite число в VARCHAR(n) не проверяется вообще: столбец получает TEXT affinity и примет строку любой длины, VARCHAR(50) не отсечет 200 символов. Нужен лимит — задавайте его явно через CHECK(length(col) <= n) или проверяйте в приложении.

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

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

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

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

По стандартной модели PRIMARY KEY — это, по сути, NOT NULL плюс UNIQUE вместе, и в большинстве СУБД под первичный ключ автоматически создается индекс. Но в SQLite запрет NULL гарантирован не всегда: он действует для INTEGER PRIMARY KEY, а также в таблицах STRICT и WITHOUT ROWID. В обычной rowid-таблице текстовый или составной первичный ключ по историческим причинам примет NULL, если столбец не объявлен явно NOT NULL (можно вставить даже несколько строк с NULL в таком ключе). Поэтому в SQLite для не-целочисленного ключа добавляйте NOT NULL сами. Столбец INTEGER PRIMARY KEY в SQLite — особый случай: он становится псевдонимом внутреннего rowid, NULL в него вставить нельзя.

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

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

Отдельно стоит CHECK — шестое полезное ограничение, которое задает допустимое множество или диапазон значений столбца. Оно и превращает соглашение «здесь только 0 или 1» в реальное правило. Тип INTEGER сам по себе примет и 2, и -1, а CHECK (paid IN (0, 1)) вставку такого значения отклонит. В примере ниже я вешаю CHECK на логический столбец paid.

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

Ниже законченная схема из двух связанных таблиц — ее можно скопировать и запустить в 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 CHECK (paid IN (0, 1)),
    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 прошла бы без ошибки и оставила «висячую» ссылку.

Записали в логический столбец значение вне множества 0/1 при ограничении CHECK:

INSERT INTO enrollments (student_id, course, paid) VALUES (1, 'Extra', 2);

Runtime error: CHECK constraint failed: paid IN (0, 1) (19)

Исправление — хранить в логическом поле только 0 или 1. Без CHECK столбец INTEGER молча принял бы 2: множество допустимых значений задает именно ограничение, а не тип.

Перенос схемы между СУБД

Учебный пример работает в SQLite, но те же сущности в серверных СУБД описывают немного иначе. Короткая таблица переноса — что менять при переезде схемы:

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

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

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

Выводы

• Создание базы — это два шага: CREATE DATABASE заводит базу (в SQLite — создание файла), CREATE TABLE описывает схему.
• Тип столбца задает допустимые значения; в серверных СУБД для денег берут DECIMAL/NUMERIC, а в SQLite тип — это лишь affinity (для точных денег храните целое число минимальных единиц).
• Базовые ограничения: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT и CHECK — они защищают целостность данных; CHECK ограничивает множество или диапазон значений (например, флаг только 0/1).
• В SQLite есть свои оговорки: DECIMAL не дает fixed-point, VARCHAR(n) не проверяет длину, а NULL в первичном ключе запрещен только для INTEGER PRIMARY KEY, STRICT и WITHOUT ROWID — в остальных случаях добавляйте NOT NULL явно.
• FOREIGN KEY в SQLite по умолчанию не проверяется, включайте PRAGMA foreign_keys = ON.
• Нормализация убирает дублирование фактов, индексы ускоряют поиск, но замедляют запись.

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

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

FAQ

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

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

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