Ключевые и уникальные поля
Создание базы данных следует начинать с детальной разработки структуры ее таблиц. Эта структура должна быть такой, чтобы при работе с базой требовалось вводить в нее как можно меньше данных. Уже имеющиеся данные, должны быть доступны для выбора при добавлении новой записи с идентичными полями. Если ручной ввод каких-то данных приходится повторять неоднократно, то структуру базы необходимо изменить, придумав другой набор связанных таблиц, устраняющий этот недостаток.
Для надежной работы связей между всеми таблицами базы данных и быстрого поиска по данным из одной таблицы всех связанных с ними записей в других таблицах, необходимо предусмотреть так называемые уникальные поля.
Уникальное поле—это поле, значения в котором не могут повторяться.
Поле Фамилияв таблицеАвторвполне может содержать нескольких Ивановых, Петровых или Сидоровых, точно также как полеИмяможет пестрить различными Аланами, Эдуардами и Робертами. Это означает, что эти поля не являются уникальными и поэтому их нельзя использовать для связи между таблицами. ПолеНазвание— более удачный кандидат на почетное звание уникального поля, но не тут то было. Многие современные авторы очень любят называть свои произведения в точности так, как это делали Пушкин, Лермонтов или Толстой. Что же тогда остается делать?
Выход всегда есть! Если ни одно поле в Вашей таблице не приемлемо как уникальное, то его можно создать искусственно. Например, введя в таблицу шифр записи. Это могут быть буквы (например, первые буквы слов названия кники), цифры или их комбинация, но самое главное — они не будут повторяться, а значит, станут уникальными для каждой записи в таблице.
Скорее всего, поле Шифрокажется уникальным и позволит создать связи между таблицами, но было бы хорошо, если бы компьютер сигнализировал нам в том случае, если вдруг записи в этом поле повторяться. Для этого вводится понятиеключевое поле. При создании структуры таблиц, можно одно поле (или одну комбинацию полей) сделать ключевым. С такими полями компьютер работает особо. Он автоматически проверяет их уникальность и значительно быстрее выполняет сортировку по таким полям. Ключевое поле в этой ситуации становится очевидным кандидатом для создания связей между таблицами. Иногда такое поле еще называютпервичным ключом.Если при создании таблицы Вы не задали ключевое поле, то СУБД вежливо напомнит о том, что первичный ключ не задан и предложит создать такое поле.
Часто в качестве уникального поля создают поле, имеющее тип Счетчик(см. типы полей в«Шаг 3 — Свойства и типы полей»). Ввести два одинаковых значения в такое поле просто невозможно. Приращение значения этого поля происходит автоматически, при добавлении новой записи в таблицу, независимо от желания создающего эту запись. Компьютер сам следит за этим полем и не позволит вносить туда какие-либо изменения.
Объекты Access
После запуска программы Access, Вам будет предложено три возможных варианта дальнейших действий. Можно выбрать из списка уже существующую базу данных (созданную в прошлых сеансах работы), создать новую базу (действуя, что называется с «нуля»), либо воспользоваться мастером создания баз данных. Для начала, просто создадим пустую базу, установив переключатель в положениеНовая база данныхи нажав кнопкуОК.
Далее СУБДзапросит имя будущей базы и сохранит ее в виде .mdbфайла, в папке указанной при помощи вспомогательного окна проводника (по умолчанию дляMS Officeэто папкаМои документы, но желательно хранить базы в отдельной, специально созданной для этих нужд папке). Имя базы данных должно описывать ее содержимое. Это необходимо для дальнейшего более простого ориентирования в массе созданных Вами файлов.
После выполнения всех подготовительных работ будет сформирована совершенно пустая БД. Исходное окно содержит шесть вкладок, представляющих шесть видов объектов, с которыми в дальнейшем и будет работать программа.
Таблицы— это основные и самые необходимые объекты любой БД. Их назначение уже рассматривалось в«Шаг 4 — Связь между таблицами». Напомню, что именно в таблицах хранятся все данные, и чтореляционная БДможет содержать целый набор взаимосвязанных таблиц.
Запросы— это специализированные структуры, создаваемые для осуществления обработки базы данных. С помощью запросов можно упорядочить данные, произвести их фильтрацию, объединение, отбор или даже изменение.
Формы— это объекты, позволяющие вводить в базу новые данные или просматривать уже существующие, в удобной для пользователя форме (виде, представлении).
Отчеты— эти объекты говорят сами за себя. Они выдают данные на принтер или другое устройство вывода (это может быть и монитор), в удобном и наглядном виде. Например, в виде бланка или счета.
Макросы— это набормакрокоманд. Когда возникает необходимость частого выполнения одних и тех же операций с БД, имеется возможность сгруппировать набор команд в одинмакрос. После чего, инициализацию его выполнения закрепляют за определенной комбинацией клавиш клавиатуры. Простыми словами, нажатие этой комбинации при работе с базой, приводит к выполнению всей последовательности действий записанных в макрос.
Модули— это программы созданные средствами языкаVisual Basic. Позволяющие дополнить стандартные средстваAccess, если уже имеющихся не хватает для удовлетворения всех требований к работеСУБД. Программист под заказ, может расширить возможности системы, дописав необходимые модули и добавив их в Вашу БД.
Уникальные и ключевые поля
Создание базы данных всегда начинается с разработки структуры ее таблиц. Структура должна быть такой, чтобы при работе с базой требовалось вводить в нее как можно меньше данных. Если ввод каких-то данных приходится повторять неоднократно, базу делают из нескольких связанных таблиц. Структуру каждой таблицы разрабатывают отдельно. Для того, чтобы связи между таблицами работали надежно и по записи из одной таблицы можно было однозначно найти записи в другой таблице, надо предусмотреть уникальные поля.
Уникальное поле — это поле, значения в котором не могут повторяться.
Если ни одно поле таблицы не приемлемо в качестве уникального, его можно создать искусственно. Если, допустим, в нашей базе данных есть таблицы с фамилиями клиентов и номерами их телефонов, в качестве уникального поля можно создать поле Шифр, которое образовано первыми двумя буквами фамилии и последними тремя цифрами номера телефона. Конечно, есть все шансы полагать, что такое поле окажется уникальным, но было бы хорошо, если бы могли как-то заметить совпадение записи в этом поле. Для этого существует ключевое поле. При создании структуры таблицы одно поле (или комбинацию полей) можно назначить ключевым. С ключевыми полями компьютер работает особо. Он проверяет их уникальность и быстрее выполняет сортировку по таким полям. Ключевое поле или первичный ключ — очевидный кандидат для создания связей. Часто в качестве первичного ключа используют поле Счетчик, поскольку ввести в это поле два одинаковых значения невозможно.
SQL UNIQUE
Ограничение UNIQUE в SQL позволяет идентифицировать каждую запись в таблице. Если помещается ограничение столбца UNIQUE в поле при создании таблицы, база данных отклонит любую попытку ввода в это поле для одной из строк, значения, которое уже представлено в другой строке. Это ограничение может применяться только к полям, которые были объявлены как непустые (NOT NULL), так как не имеет смысла позволить одной строке таблицы иметь значение NULL, а затем исключать другие строки с NULL значениями как дубликаты.
SQL Server / Oracle / Access
Пример создания таблицы SQL с ограничением UNIQUE:
Когда обьявляется поле Fam уникальным, две Смирновых Марии могут быть введены различными способами — например, Смирнова Мария и Смирнова М. Столбцы (не первичные ключи), чьи значения требуют уникальности, называются ключами-кандидатами или уникальными ключами. Можно определить группу полей как уникальную с помощью команды ограничения таблицы — UNIQUE. Объявление группы полей уникальной, отличается от объявления уникальными индивидуальных полей, так как это комбинация значений, а не просто индивидуальное значение, которое обязано быть уникальным. Уникальность группы заключается в том, что пары строк со значениями столбцов «a», «b» и «b», «a» рассматривались отдельно одна от другой.
Если база данных определяет, что каждая специальность принадлежит одному и только одному факультету, то каждая комбинация кода факультета(Kod_f) и кода специальности(Kod_spec) в таблице Spec должна быть уникальной. Например:
Оба поля в ограничении таблицы UNIQUE все еще используют ограничение столбца — NOT NULL. Если бы использовалось ограничение столбца UNIQUE для поля Kod_spec, такое ограничение таблицы было бы необязательным. Если значения поля Kod_spec различно для каждой строки, то не может быть двух строк с идентичной комбинацией значений полей Kod_spec и Kod_f.
Ограничение таблицы UNIQUE наиболее полезно, когда индивидуальные поля не обязательно должны быть уникальными.
MySQL UNIQUE
Пример создания таблицы Persons в MySQL с ограничением UNIQUE:
Удалить ограничение UNIQUE
Если после создания ограничения UNIQUE и в том случае, когда ограничение UNIQUE не имеет смысла, UNIQUE можно удалить. Для этого используйте следующий SQL: SQL Server / Oracle / MS Access:
Prisma ORM: полное руководство для начинающих (и не только). Часть 1
В этой серии из 2 статей я хочу поделиться с вами своими заметками о Prisma .
Prisma — это современное (продвинутое) объектно-реляционное отображение (Object-Relational Mapping, ORM) для Node.js и TypeScript . Проще говоря, Prisma — это инструмент, позволяющий работать с реляционными ( PostgreSQL , MySQL , SQL Server , SQLite ) и нереляционной ( MongoDB ) базами данных с помощью JavaScript или TypeScript без использования SQL (хотя такая возможность имеется).
Содержание этой части
Если вам это интересно, прошу под кат.
Инициализация проекта
Создаем директорию, переходим в нее и инициализируем Node.js-проект :
Устанавливаем Prisma в качестве зависимости для разработки:
Инициализируем проект Prisma :
Это приводит к генерации файлов prisma/schema.prisma и .env .
В файле .env содержится переменная DATABASE_URL , значением которой является путь к (адрес) БД. Файл schema.prisma мы рассмотрим позже.
Интерфейс командной строки (Command line interface, CLI) Prisma предоставляет следующие основные возможности (команды):
- init — создает шаблон Prisma-проекта :
- —datasource-provider — провайдер для работы с БД: sqlite , postgresql , mysql , sqlserver или mongodb (перезаписывает datasource из schema.prisma );
- —url — адрес БД (перезаписывает DATABASE_URL )
- generate — генерирует клиента Prisma на основе схемы ( schema.prisma ). Клиент Prisma предоставляет программный интерфейс приложения (Application Programming Interface, API) для работы с моделями и типы для TypeScript
- db pull — генерирует модели на основе существующей схемы БД
- db push — синхронизирует состояние схемы Prisma с БД без выполнения миграций. БД создается при отсутствии. Используется для прототипировании БД и в локальной разработке. Также может быть полезной в случае ограниченного доступа к БД, например, при использовании БД, предоставляемой облачными провайдерами, такими как ElephantSQL или Heroku
- seed — выполняет скрипт для наполнения БД начальными (фиктивными) данными. Путь к соответствующему файлу определяется в package.json
- migrate
- dev — выполняет миграцию для разработки:
- —name — название миграции
Это приводит к созданию БД при ее отсутствии, генерации файла prisma/migrations/migration_name.sql , выполнению инструкции из этого файла (синхронизации БД со схемой) и генерации (регенерации) клиента ( prisma generate ).
Данная команда должна выполняться после каждого изменения схемы.
- reset — удаляет и заново создает БД или выполняет «мягкий сброс», удаляя все данные, таблицы, индексы и другие артефакты
- deploy — выполняет производственную миграцию
- studio — позволяет просматривать и управлять данными, хранящимися в БД, в интерактивном режиме:
- —browser , -b — название браузера (по умолчанию используется дефолтный браузер);
- —port , -p — номер порта (по умолчанию — 5555 )
Подробнее о CLI можно почитать здесь.
Схема
В файле schema.prisma мы видим такие строки:
- datasource — источник данных:
- provider — название провайдера для доступа к БД: sqlite , postgresql , mysql , sqlserver или mongodb (по умолчанию — postgresql );
- url — адрес БД (по умолчанию — значение переменной DATABASE_URL );
- shadowDatabaseUrl — адрес «теневой» БД (для БД, предоставляемых облачными провайдерами): используется для миграций для разработки ( prisma migrate dev );
- provider — провайдер генератора (единственным доступным на сегодняшний день провайдером является prisma-client-js );
- binaryTargets — определяет операционную систему для клиента Prisma . Значением по умолчанию является native , но иногда это приходится указывать явно, например, при использовании клиента в Docker-контейнере (в этом случае также приходится явно выполнять prisma generate )
Для работы со схемой удобно пользоваться расширением Prisma для VSCode . Соответствующий раздел в файле settings.json должен выглядеть так:
Определим в схеме модели для пользователя ( User ) и поста ( Post ):
Вот что мы здесь видим:
- id , email , hash etc. — названия полей (колонок таблицы);
- @map привязывает поле схемы ( hash ) к указанной колонке таблицы ( password_hash ). @map не меняет название колонки в БД и поля в генерируемом клиенте. Для MongoDB использование @map для @id является обязательным: id String @default(auto()) @map(«_id») @db.ObjectId ;
- String , Int , DateTime etc. — типы данных (см. ниже);
- @db.Uuid — тип данных, специфичный для одной или нескольких БД (в данном случае PostgreSQL );
- модификатор ? после названия типа означает, что данное поле является опциональным (необязательным, может иметь значение NULL );
- модификатор [] после названия типа означает, что значением данного поля является список (массив). Такое поле не может быть опциональным;
- префикс @ означает атрибут поля, а префикс @@ — атрибут блока (модели, таблицы). Некоторые атрибуты принимают параметры;
- атрибут @id означает, что данное поле является первичным (основным) ключом таблицы ( PRIMARY KEY ) (идентификатор модели). Такое поле не может быть опциональным;
- атрибут @default присваивает полю указанное значение по умолчанию (при отсутствии значения поля) ( DEFAULT ). Дефолтными могут быть статические значения ( 42 , hi ) или значения, генерируемые функциями autoincrement , dbgenerated , cuid , uuid и now (функции атрибутов; см. ниже);
- атрибут @unique означает, что значение поля должно быть уникальным в пределах таблицы ( UNIQUE ). Таблица должна иметь хотя бы одно поле @id или @unique ;
- атрибут @relation указывает на существование отношений между таблицами. В данном случае между таблицами users и posts существуют отношения один-ко-многим (one-to-many, 1-n) — у одного пользователя может быть несколько постов ( FOREIGN KEY / REFERENCES ) (об отношениях мы поговорим отдельно);
- атрибут @updatedAt обновляет поле текущими датой и временем при любой модификации записи;
- у нас имеется перечисление (enum), значения которого используются в качестве значений поля role модели User (значением по умолчанию является USER );
- атрибут @@map привязывает название модели к названию таблицы в БД. @@map не меняет название таблицы в БД и модели в генерируемом клиенте.
Типы данных
Допустимыми в названиях полей являются следующие символы: [A-Za-z][A-Za-z0-9_]* .
- String — строка переменной длины (для PostgreSQL — это тип text );
- Boolean — логическое значение: true или false ( boolean );
- Int — целое число ( integer );
- BigInt — BigInt ( integer );
- Float — число с плавающей точкой (запятой) ( double precision );
- Decimal ( decimal(65,30) );
- DateTime — дата и время в формате ISO 8601 ;
- Json — объект в формате JSON ( jsonb );
- Bytes ( bytea ).
Атрибут @db позволяет использовать типы данных, специфичные для одной или нескольких БД.
Атрибуты
Кроме упомянутых выше, в схеме можно использовать следующие атрибуты:
- @@id — определяет составной (composite) первичный ключ таблицы, например, @@id[title, author] (в данном случае соответствующее поле будет называться title_author — это можно изменить);
- @@unique — определяет составное ограничение уникальности (unique constraint) для указанных полей (такие поля не могут быть опциональными), например, @@unique([title, author]) ;
- @@index — определяет индекс в БД ( INDEX ), например, @@index([title, author]) ;
- @ignore , @@ignore — используется для обозначения невалидных полей и моделей, соответственно.
Функции атрибутов
- auto — представляет дефолтные значения, генерируемые БД (только для MongoDB );
- autoincrement — генерирует последовательные целые числа ( SERIAL в PostgreSQL , не поддерживается MongoDB );
- cuid — генерирует глобальный уникальный идентификатор на основе спецификации cuid ;
- uuid — генерирует глобальный уникальный идентификатор на основе спецификации UUID ;
- now — возвращает текущую отметку времени (timestamp) ( CURRENT_TIMESTAMP в PostgreSQL );
- dbgenerated — представляет дефолтные значения, которые не могут быть выражены в схеме (например, random() ).
Подробнее о схеме можно почитать здесь.
Отношения
Атрибут @relation указывает на существование отношений между моделями (таблицами). Он принимает следующие параметры:
- name?: string — название отношения;
- fields?: [field1, field2, . fieldN] — список полей текущей модели (в нашем случае это [author_id] модели Post ); обратите внимание: само поле определяется отдельно);
- references: [field1, field2, . fieldN] — список полей другой модели (стороны отношений) (в нашем случае это [id] модели User ).
В приведенной выше схеме полями, указывающими на существование отношений между моделями User и Post , являются поля posts и author . Эти поля существуют только на уровне Prisma , в БД они не создаются. Скалярное поле author_id также существует только на уровне Prisma — это внешний ключ ( FOREIGN KEY ), соединяющий Post с User .
Как известно, существует 3 вида отношений:
- один-к-одному (one-to-one, 1-1);
- один-ко-многим (one-to-many, 1-n);
- многие-ко-многим (many-to-many, m-n).
Атрибут @relation является обязательным только для отношений 1-1 и 1-n .
Предположим, что в нашей схеме имеются такие модели:
Вот что мы здесь видим:
- между моделями User и Profile существуют отношения 1-1 — у одного пользователя может быть только один профиль;
- между моделями User и Post существуют отношения 1-n — у одного пользователя может быть несколько постов;
- между моделями Post и Category существуют отношения m-n — один пост может принадлежать к нескольким категориям, в одну категорию может входить несколько постов.
Подробнее об отношениях можно почитать здесь.
Клиент
Импортируем и создаем экземпляр клиента Prisma :
Иногда может потребоваться делать так:
Запросы
findUnique
findUnique позволяет извлекать единичные записи по идентификатору или уникальному полю.
Сигнатура
Модификатор ? означает, что поле является опциональным.
- condition — условие для выборки;
- fields — поля для выборки;
- relations — отношения (связанные поля) для выборки;
- rejectOnNotFound — если имеет значение true , при отсутствии записи выбрасывается исключение NotFoundError . Если имеет значение false , при отсутствии записи возвращается null .
Пример
findFirst
findFirst возвращает первую запись, соответствующую заданному критерию.
Сигнатура
- distinct — фильтрация по определенному полю;
- orderBy — сортировка по определенному полю и в определенном порядке;
- cursor — позиция начала списка (как правило, id или другое уникальное значение);
- skip — количество пропускаемых записей;
- take — количество возвращаемых записей (в данном случае может иметь значение 1 или -1 : во втором случае возвращается последняя запись.
Пример
findMany
findMany возвращает все записи, соответствующие заданному критерию.
Сигнатура
Пример
create
create создает новую запись.
Сигнатура
- _data — данные создаваемой записи.
Пример
update
update обновляет существующую запись.
Сигнатура
Пример
upsert
upsert обновляет существующую или создает новую запись.
Сигнатура
Пример
delete
delete удаляет существующую запись по идентификатору или уникальному полю.
Сигнатура
Пример
createMany
createMany создает несколько записей с помощью одной транзакции (о транзакциях мы поговорим отдельно).
Пример
- _data[] — данные для создаваемых записей в виде массива;
- skipDuplicates — при значении true создаются только уникальные записи.
Пример
updateMany
updateMany обновляет несколько существующих записей за один раз и возвращает количество (sic) обновленных записей.
Сигнатура
Пример
deleteMany
deleteMany удаляет несколько записей с помощью одной транзакции и возвращает количество удаленных записей.
Сигнатура
Пример
count
count возвращает количество записей, соответствующих заданному критерию.
Сигнатура
Пример
aggregate
aggregate выполняет агрегирование полей.
Сигнатура
- _count — возвращает количество совпадающих записей или не null-полей ;
- _avg — возвращает среднее значение определенного поля;
- _sum — возвращает сумму значений определенного поля;
- _min — возвращает наименьшее значение определенного поля;
- _max — возвращает наибольшее значение определенного поля.
Пример
groupBy
groupBy выполняет группировку полей.
Сигнатура
- by — определяет поле или комбинацию полей для группировки записей;
- having — позволяет фильтровать группы по агрегируемому значению.
Пример
В следующем примере мы выполняем группировку по country / city , где среднее значение profileViews превышает 100 , и возвращаем общее количество ( _sum ) profileViews для каждой группы. Запрос также возвращает количество всех ( _all ) записей в каждой группе и все записи с не null значениями поля city в каждой группе: