Проектирование баз данных. Реализация с учётом ограничений / Хабр

Привет, Хаброжители! Мы открыли предзаказ на книгу «Грокаем проектирование реляционных баз данных» Цян Хао и Михаила Цикердекиса. Предлагаем ознакомиться с отрывком «Реализация».

Завершив нормализацию базы данных, перейдем к следующему этапу — реализации. Знаний, полученных еще в главах 1 и 2, вполне достаточно, чтобы без особых усилий перевести диаграмму проекта на SQL и создать все нужные таблицы. К примеру, таблица user на диаграмме выглядит так:

Чтобы ее создать, потребуется следующий код на языке SQL:

-- comment: команда для MySQL и MariaDB
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE user (
  email VARCHAR(320) PRIMARY KEY,
  username VARCHAR(30),
  password VARCHAR(20),
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone_number VARCHAR(15),
  last_login_time TIMESTAMP
);

На этом все? Не совсем. Успешная реализация проекта потребует близкого знакомства с ограничениями. Умение работать с ними позволит принимать решения по вопросам, лежащим за пределами ER-диаграмм.

В главе 2 мы уже применяли ограничения, но так и не дали им формального определения. В языке SQL ограничения (constraints) — это правила, которые применяются к столбцам таблицы для обеспечения точности и достоверности данных. Ограничения гарантируют целостность данных и могут вводиться как при создании таблицы, так и на более поздних этапах работы. Переживаете, что разработчики приложения будут работать с базой не так, как вы планировали (слоны в посудной лавке)? Введите ограничения, которые им придется соблюдать.

В следующих разделах нас ждет подробный разговор об ограничениях. Конечно же, не обо всех, а лишь о самых распространенных. Мы также обсудим, какие еще решения нужно принять в ходе реализации. При этом мы не будем рассматривать весь SQL-скрипт для создания базы данных: желающие могут найти его в репозитории GitHub (https://mng.bz/4ao5).

NOT NULL: всегда есть что сказать

Ограничение NOT NULL гарантирует, что в столбце не будет пустых значений. Если оно введено, при загрузке данных такой столбец нельзя пропустить. Как мы уже знаем из главы 1, NULL — это неизвестное значение, из-за которого при выполнении скриптов часто возникают ошибки. Поэтому NOT NULL — весьма удобный способ предотвратить проблемы с хранением и анализом данных.

Когда же нужно вводить ограничение NOT NULL? Почти всегда. К примеру, без него не обойтись, если в столбце хранятся важные для бизнеса данные (имена пользователей, адреса электронной почты, пароли и т. п.) или же сведения, наличие которых обязательно по закону или стандартам (к примеру, даты рождения в системах с ограничением по возрасту). Как правило, NOT NULL вводится для внешних ключей связанных таблиц. Чуть позже мы будем рассматривать такие случаи.

Понадобится ли NOT NULL в базе данных «Фантастической лавки»? Конечно да. В нашем проекте немало столбцов с данными, критически важными для бизнеса. К примеру, в таблице user таких столбцов шесть: username, email, password, first_name, last_name и last_login_time.

Чтобы ввести ограничение NOT NULL, необходимо добавить эти слова в определение столбца:

-- comment: команда для MySQL, MariaDB и SQLite
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE user (
  email VARCHAR(320) PRIMARY KEY,
  username VARCHAR(30) NOT NULL,
  password VARCHAR(20) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  phone_number VARCHAR(15),
  last_login_time TIMESTAMP NOT NULL
);

Обратите внимание: определение столбца email не изменилось. Все дело в том, что это первичный ключ, а значит, действует другое ограничение — PRIMARY KEY, которое автоматически включает в себя и NOT NULL.

PRIMARY KEY: единственный и неповторимый

Как мы уже знаем, в хорошем проекте в каждой таблице имеется особый столбец — первичный ключ. На языке SQL такие столбцы получают ограничение PRIMARY KEY, которое гарантирует отсутствие повторяющихся строк, а точнее, строк с одинаковым значением первичного ключа. Кроме того, не допускается наличие строк с первичным ключом, равным NULL.

Синтаксис ограничения очень прост: в определение столбца необходимо добавить слова PRIMARY KEY:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE user (
  email VARCHAR(320) PRIMARY KEY,
  username VARCHAR(30) NOT NULL,
  password VARCHAR(20) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  phone_number VARCHAR(15),
  last_login_time TIMESTAMP NOT NULL
);

Если проект базы данных еще не завершен и есть вероятность, что первичный ключ изменится, можно задать имя ограничения явным образом при помощи блока CONSTRAINT, который дополняет определение столбца, как в следующем фрагменте:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE user (
  email VARCHAR(320),
  username VARCHAR(30) NOT NULL,
  password VARCHAR(20) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  phone_number VARCHAR(15),
  last_login_time TIMESTAMP NOT NULL,
  CONSTRAINT pk_user PRIMARY KEY (email)
);

Именование ограничений: лучшие практики
В языке SQL давать имена ограничениям не обязательно, но, в силу следующих причин очень желательно:
Имена ограничений четко указывают их назначение, а это делает схему базы данных более понятной для всех, кому придется в ней разбираться.
Если ограничение будет нарушено, его имя появится в сообщении об ошибке. Такие сообщения более информативны и позволяют быстрее устранить проблему.
При отсутствии явно указанного имени СУБД сформирует его автоматически. Однако такие имена обычно не слишком осмысленны, а их формат зависит от СУБД, что может вызвать путаницу, особенно если понадобится перенести базу данных в другую систему.
Некоторые ограничения, к примеру NOT NULL и DEFAULT, как правило, не имеют имен, поскольку вводятся при определении столбцов: такой подход облегчает и разработку, и чтение SQL-скриптов. Что же касается других ограничений, прежде всего PRIMARY KEY и FOREIGN KEY, то если есть хоть малейшая вероятность изменения или же переноса базы в другую СУБД, лучше всего заранее дать им явные имена.

А как быть с составными первичными ключами? Ключ из нескольких столбцов можно определить в отдельном блоке SQL, как показано ниже. В базе данных «Фантастической лавки» как раз имеется такая таблица — purchase_product, первичный ключ которой состоит из двух столбцов: purchase_id и code.

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE purchase_product (
  purchase_id INT NOT NULL,
  code CHAR(12) NOT NULL,
  product_price DECIMAL(7,2) NOT NULL,
  product_quantity INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  CONSTRAINT pk_purchase_product
  PRIMARY KEY (purchase_id, code)
);

FOREIGN KEY: дружба навек

Как мы уже знаем, внешний ключ, который обычно состоит из одного столбца, поддерживает связь между двумя таблицами. На языке SQL ему соответствует ограничение FOREIGN KEY.

Ограничение по внешнему ключу обеспечивает ссылочную целостность. Что это такое? Чтобы связать друг с другом две таблицы, в одну из них нужно добавить внешний ключ — ссылку на первичный ключ второй. При этом первая таблица станет дочерней, а вторая — родительской. Попытка добавить в дочернюю таблицу запись со ссылкой на строку, которой нет в родительской, закончится сообщением об ошибке. Мы говорили об этом в главе 2.

Между таблицами user и review из базы данных «Фантастической лавки» имеется связь «один ко многим», которую поддерживает внешний ключ email таблицы review. При правильной реализации ограничения FOREIGN KEY СУБД заблокирует добавление отзыва, если пользователь (точнее, его электронная почта) отсутствует в таблице user.

С точки зрения реализации внешний ключ очень похож на первичный. К примеру, в таблице review будет два внешних ключа: для таблиц user и product. Можно определить и назвать их сразу при создании таблицы:

-- comment: команда для MySQL и MariaDB
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE review (
  review_id INT PRIMARY KEY,
  review_text TEXT NOT NULL,
  review_time TIMESTAMP NOT NULL,
  email VARCHAR(320) NOT NULL,
  code CHAR(12) NOT NULL,
  CONSTRAINT fk_user_review
    FOREIGN KEY (email) REFERENCES user(email),
  CONSTRAINT fk_product_review
    FOREIGN KEY (code) REFERENCES product(code)
);

Или же можно добавить ограничения в уже существующую таблицу:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: SQLite не поддерживает команду ALTER TABLE ADD CONSTRAINT
-- этап 1: создание таблицы review
CREATE TABLE review (
  review_id INT PRIMARY KEY,
  review_text TEXT NOT NULL,
  review_time TIMESTAMP NOT NULL,
  email VARCHAR(320) NOT NULL,
  code CHAR(12) NOT NULL
);
-- этап 2: добавление ограничений по внешнему ключу
ALTER TABLE review
  ADD CONSTRAINT fk_user_review
    FOREIGN KEY (email) REFERENCES user(email),
  ADD CONSTRAINT fk_product_review
    FOREIGN KEY (code) REFERENCES product(code)

Так же как и в случае с первичным ключом, блок CONSTRAINT позволяет дать ограничению имя, а блоки FOREIGN KEY и REFERENCES создают внешний ключ, который обеспечит ссылочную целостность данных в двух таблицах.

Нужно отметить, что если связь между таблицами обязательна (то есть ее минимальная кратность в каком-то из направлений равна единице), на внешний ключ также полезно наложить ограничение NOT NULL. К примеру, такова связь между таблицами review и user, поскольку минимальная кратность в направлении последней равна единице (у отзыва должен быть хотя бы один автор). Действительно, добавить в таблицу review запись, которая не ссылается ни на кого из пользователей из таблицы user, не получится. Поскольку NULL — это заведомо нерабочая ссылка, разрешать такие значения внешнего ключа не имеет смысла.

Ссылочные действия

Ранее мы говорили о том, что ограничение по внешнему ключу гарантирует ссылочную целостность при добавлении данных. Если при вставке строки в дочернюю таблицу выясняется, что в родительской нет записи, на которую ссылается внешний ключ, такая операция блокируется: возникает ошибка.

Но ссылочную целостность нужно обеспечивать не только при добавлении данных. Что делать при удалении записи из родительской таблицы? Как быть, когда в ней изменяется значение первичного ключа? Что в этих случаях должно произойти со строками дочерней таблицы?

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

ON DELETE RESTRICT
ON UPDATE RESTRICT

Оба словосочетания определяют, что делать с записями дочерней таблицы: ON DELETE — при удалении записей из родительской таблицы, ON UPDATE — при изменении значений первичного ключа в родительской таблице. Такие действия называются ссылочными (referential actions) и выполняются автоматически. RESTRICT (или, что то же самое, NO ACTION) — действие по умолчанию, которое заключается в том, что удаление либо изменение строк родительской таблицы полностью блокируется.

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

Каскадное удаление. Выполняется при удалении строки из родительской таблицы. СУБД автоматически находит в дочерней таблице все записи со ссылкой на эту строку и удаляет их (если это возможно). Благодаря этому удаление не приведет к появлению «потерянных» строк с нерабочими ссылками.
Каскадное обновление. Выполняется при изменении первичного ключа в родительской таблице. СУБД автоматически находит в дочерней таблице все записи со ссылкой на эту строку и обновляет значение внешнего ключа. Благодаря этому ссылки на записи в родительской таблице продолжают работать без ручного вмешательства.

Каскадное удаление и обновление просты в реализации: достаточно изменить блок CONSTRAINT, добавив в него новые команды:

ON DELETE CASCADE
ON UPDATE CASCADE

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

В более редких случаях может потребоваться удалить запись из родительской таблицы, но сохранить все записи в дочерней. К примеру, в базе данных имеется две связанные таблицы: device (устройство) и employee (сотрудник). Когда сотрудник увольняется из компании, запись о нем необходимо удалить из таблицы employee. Однако устройства, которыми он пользовался, все еще на балансе компании, а потому из таблицы device удалять ничего не требуется. В подобных случаях можно использовать ссылочное действие SET NULL:

ON DELETE SET NULL
ON UPDATE SET NULL

При удалении строки из родительской таблицы СУБД автоматически находит в дочерней все записи со ссылкой на эту строку и обновляет в них внешний ключ: заменяет старое значение на NULL. Это довольно удобно, когда связь между двумя таблицами необязательна или же нужно сохранить информацию как, например, архивную. Однако появление NULL чревато проблемами в виде «потерянных» строк и усложнения запросов, ожидающих ненулевые внешние ключи.

Понадобятся ли ссылочные действия в базе данных «Фантастической лавки»? Да. К примеру, таблица user с первичным ключом email является родительской по отношению к нескольким другим, в том числе payment_method.

Если бы клиенты «Фантастической лавки» имели возможность сменить электронную почту или же полностью удалить учетную запись, нам бы пришлось обеспечивать целостность данных при удалении строк из таблицы user и обновлении ее первичного ключа email. Для этого можно добавить действие CASCADE, к примеру, в таблицу payment_method:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE payment_method (
  payment_id INT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  card_number CHAR(16) NOT NULL,
  expiry_date CHAR(4) NOT NULL,
  email VARCHAR(320) NOT NULL,
  CONSTRAINT fk_payment_method_user
    FOREIGN KEY (email) REFERENCES user (email)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

UNIQUE: простите, я занят

Ограничение UNIQUE гарантирует, что все значения столбца или сочетания столбцов будут уникальными. Использовать его имеет смысл, когда такое требование предъявляется к столбцу или столбцам, не входящим в первичный ключ.

Представим, что в таблице есть столбец с номерами социального страхования (SSN) — уникальными девятизначными идентификаторами, которые правительство США использует для контроля за гражданами: их доходами, налогами или, допустим, правами на льготы. Этот столбец не является частью ключа. Однако поскольку каждый номер представляет конкретного человека, у нескольких человек одинакового SSN быть не может. В подобных случаях стоит ввести ограничение UNIQUE.

У нас в базе данных такие столбцы тоже есть. Это несложно понять, если вернуться к анализу требований. Таблица user выглядит так:

Мы знаем, что значения в столбцах username и phone_number должны быть уникальными. Однако поскольку они не входят в первичный ключ, добиться этого можно при помощи ограничений UNIQUE. Добавим их, не забывая об именах:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE user (
  email VARCHAR(320) PRIMARY KEY,
  username VARCHAR(30) NOT NULL,
  password VARCHAR(20) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  phone_number VARCHAR(15),
  last_login_name TIMESTAMP NOT NULL,
  CONSTRAINT unq_username UNIQUE(username),
  CONSTRAINT unq_phone_number UNIQUE(phone_number)
);

Бывают случаи, когда ограничение UNIQUE должно распространяться на сочетание столбцов. Вот, например, таблица product:

Если, согласно требованиям, необходимо обеспечить уникальность комбинаций двух свойств сущности, нужно ввести ограничение UNIQUE на соответствующую пару столбцов:

-- comment: команда для MySQL, MariaDB и PostgreSQL
-- comment: команду для других СУБД можно найти в репозитории
CREATE TABLE product (
  code CHAR(12) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT NOT NULL,
  manufacturer VARCHAR(100) NOT NULL,
  photo VARCHAR(1000) NOT NULL,
  price DECIMAL(7,2) NOT NULL,
  cost DECIMAL(7,2) NOT NULL,
  inventory_quantity INT,
  CONSTRAINT unq_name_manufacturer
    UNIQUE(name, manufacturer)
);

Оформить предзаказ на книгу «Грокаем проектирование реляционных баз данных» со скидкой 35% можно на нашем сайте по промокоду - Предзаказ

Проектирование баз данных. Реализация с учётом ограничений

Публикации

Информация