Ограничения первичных и внешних ключейprimary and foreign key constraints

AUTO_INCREMENT

Когда столбец определяется с помощью AUTO_INCREMENT, его значение автоматически увеличивается каждый раз, когда в таблицу добавляется новая запись. Это удобно при использовании столбца в качестве первичного ключа. Благодаря AUTO_INCREMENTне нужно писать инструкции SQL для вычисления уникального идентификатора для каждой строки.

AUTO_INCREMENT может быть присвоен только одному столбцу в таблице. И он должен быть проиндексирован (например, объявлен в качестве первичного ключа).

Значение AUTO_INCREMENT для столбца можно переопределить, указав новое при выполнении инструкции INSERT.

Можно запросить у MySQL самое последнее значение AUTO_INCREMENT, используя функцию last_insert_id() следующим образом:

SELECT last_insert_value();

Что такое первичный ключ в БД

В базе данных первичный ключ таблицы – это один из ее столбцов (Primary key). Разберемся на примере, как это выглядит. Представим простое отношение студентов университета (назовем его «Студенты»).

Фамилия	Имя	Год рождения	Факультет	Курс	Средний балл
Иванов	Иван	1999	Экономический	3	3,8
Кузнецов	Петр	2000	Журналистики	2	4,1
Михайлов	Сергей	2000	Технологический	2	4,0
Козлов	Евгений	1998	Технологический	4	4,5
Иванов	Антон	2000	Юридический	2	3,6

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

Что такое нормализация

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

Избыточность данных — это когда одни и те же данные хранятся в базе сразу в нескольких местах.

Проверим наш пример на избыточность

Каждая строка таблицы Messages содержит имя клиента и никнейм оператора, а также их телефоны. Причём в 1-й и 3-й строках мы видим звонки от одного и того же клиента, а в 1-й и во 2-й — ответы одного и того же менеджера. То есть в 1-й и 3-й строках дублируются имя и телефон клиента — Васи, а в 1-й и 2-й — никнейм менеджера «Оператор1».

Чтобы избавиться от дублирования информации, выделим сущности Клиент и Оператор. И вынесем специфичные для каждой атрибуты в отдельные таблицы.

В первой (Clients) будут храниться имена и телефоны клиентов, а во второй (Operators) — операторов. Кроме того, каждой записи в этих таблицах мы присвоим атрибут id — так называемый первичный ключ (его значение уникально, то есть не может повторяться в пределах таблицы). С его помощью мы установим связь с записями таблицы Messages.

Для этого к каждой записи в Messages (напомним, она всё ещё представляет сущность «звонок») добавим два новых атрибута (внешних ключа): id_client и id_oper. Они будут ссылаться на первичные ключи из таблиц Clients и Operators соответственно. Столбцы с именами и телефонами из таблицы Messages уберём.

И вот что получим:

В такой базе, чтобы поменять телефон клиента сразу для всех записей, достаточно изменить всего одно поле в таблице Clients.

Всего существует шесть форм нормализации реляционных баз данных — в порядке уменьшения избыточности отношений. Все они описаны формальными правилами. Наше отношение мы привели ко второй нормальной форме.

1.2.5. Первичный ключ

Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных Microsoft SQL Server и MS Access. В таблицах Paradox такой трюк не пройдет и без наличия ключевого поля таблица будет доступна только для чтения.

В какой-то степени ключи являются ограничениями, и их можно было рассматривать вместе с оператором CHECK, потому что объявление происходит схожим образом и даже используется оператор CONSTRAINT. Давайте посмотрим на этот процесс на примере. Для этого создадим таблицу из двух полей «guid» и «vcName». При этом поле «guid» устанавливается как первичный ключ:

CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Самое вкусное здесь это строка CONSTRAINT. Как мы знаем, после этого ключевого слова идет название ограничения, и объявления ключа не является исключением. Для именования первичного ключа, я рекомендую использовать именование типа PK_имя, где имя – это имя поля, которое должно стать главным ключом. Сокращение PK происходит от Primary Key (первичный ключ).

После этого, вместо ключевого слова CHECK, которое мы использовали в ограничениях, стоит оператор PRIMARY KEY, Именно это указывает на то, что нам необходима не проверка, а первичный ключ. В скобках указывается одно, или несколько полей, которые будут составлять ключ.

Помните, что в ключевом поле не может быть одинакового значения у двух строк, в этом ограничение первичного ключа идентично ограничению уникальности. Это значит, что если сделать поле для хранения фамилии первичным ключом, то в такую таблицу нельзя будет записать двух Ивановых с разными именами. Это нарушает ограничение первичного ключа. Именно поэтому ключи являются ограничениями и объявляются также как и ограничение CHECK. Но это не верно только для первичных ключей и вторичных с уникальностью.

В данном примере, в качестве первичного ключа выступает поле типа uniqueidentifier (GUID). Значение по умолчанию для этого поля – результат выполнения серверной процедуры NEWID.

Внимание

Только один первичный ключ может быть создан для таблицы

Для простоты примеров, в качестве ключа желательно использовать численный тип и если позволяет база данных, то будет лучше, если он будет типа «autoincrement» (автоматически увеличивающееся/уменьшающееся число). В MS SQL Server таким полем является IDENTITY, а в MS Access это поле типа «счетчик».

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

CREATE TABLE Товары
(
  id int IDENTITY(1, 1),
  товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY (id)
)

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

Первичный ключ может состоять из более, чем одной колонки. Следующий пример создает таблицу, в которой поля «id» и «Товар» образуют первичный ключ, а значит, будет создан индекс уникальности на оба поля:

CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, )
)

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

Единственный недостаток первичного ключа из нескольких колонок – проблемы создания связей. Тут приходиться выкручиваться различными методами, но проблема все же решаема. Достаточно только ввести поле типа uniqueidentifier и производить связь по нему. Да, в этом случае у нас получаются уникальными первичный ключ и поле типа uniqueidentifier, но эта избыточность в результате не будет больше, чем та же таблица, где первичный ключ uniqueidentifier, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать.

Добавляем таблицы в базу данных

Очевидно, что информация, в данный момент находящаяся в базе дан­ных, является недостаточной, поскольку не известно, из каких пунк­тов состоит каждый заказ. Если помните, составляющие заказов были опущены сознательно, при этом мы обещали вернуться к этой пробле­ме позже. Пришло время распределить товары по заказам.

Проблема состоит в том, что заранее не известно, сколько пунктов со­держит каждый заказ (аналогично тому, как заранее не известно, сколько заказов может разместить каждый из клиентов). Заказ мо­жет включать в себя один, два или даже сто товаров. Необходимо отде­лить информацию о том, что клиент сделал заказ, от деталей самого заказа. Следует попытаться создать нечто, подобное изображенному на рис. 2:

Рис. 2. Расширяем базу данных «Клиенты и заказы»

Как и при создании таблиц customer и orderinfo, разделим информацию на две таблицы, а затем объединим их. Правда при этом возникает одна небольшая проблема.

Если тщательно проанализировать отношения между заказом и от­дельными товарами, станет ясно, что не только каждая запись из or- derinfo может быть связана со многими товарами, но и один и тот же товар может присутствовать во многих заказах (если несколько клиен­тов заказали один и тот же товар). Попытаемся изобразить эти две таб­лицы на диаграмме сущностей (рис. 3):

Рис. 3. Отношение между таблицами заказов и товаров

Представлено отношение «многие-ко-многим» — каждый заказ может ссылаться на множество товаров, и каждый товар может появляться во множестве заказов. Это подрывает некоторые из основ реляцион­ных баз данных. Пока не будем вдаваться в детали, заметим только, что в реляционной базе данных никакие две таблицы не должны нахо­дится в отношениях «многие-ко-многим».

Можно попытаться обойти эту проблему, создав отдельную запись в каждой строке таблицы item для каждого заказа, но тогда придется многократно повторить описание и информацию о цене для каждого товара, что нарушило бы правило третье (удалять повторяющуюся ин­формацию!).

Что касается на­шей проблемы, то для нее существует стандартное решение. Необхо­димо создать третью таблицу между двумя существующими, она будет реализовывать отношение «многие-ко-многим». Надо сказать, что объ­яснять сложнее, чем делать, поэтому просто создадим эту третью таб­лицу, , которая свяжет заказы с товарами.

Создана таблица, строки которой соответствуют каждой строчке зака­за (рис. 4). Для каждой отдельной строки можно определить, из ка­кого она заказа, по столбцу , а товар, на который она ссы­лается, — по столбцу .

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

Рис. 4. Добавление в базу данных таблицы

Обратите внимание, что в новую таблицу не введен уникальный иден­тификатор для каждой строки. Дело в том, что комбинация orderin- fo_id и всегда уникальна

Но и здесь есть одна почти незамет­ная проблема. Что произойдет, если клиент закажет два экземпляра товара в одном заказе?

Просто ввести еще одну строку в нельзя, поскольку комбина­ция и должна быть уникальной. Неужели придет­ся добавить еще одну специальную таблицу для обслуживания зака­зов, содержащих несколько экземпляров одного товара? К счастью, нет. Есть гораздо более простой способ. Надо просто ввести поле «ко­личество» (quantity) в таблицу , и все будет хорошо.

Какие реляционные БД популярны в веб-разработке

MySQL

Это открытая СУБД, купленная Oracle в придачу к Sun Microsystems. С ней работают более половины (55,6%) всех разработчиков (по  опроса, который в 2020 году провёл сайт StackOverflow.com среди 65 тысяч респондентов).

Главные её преимущества — бесплатность и высокая скорость работы с данными. MySQL создавалась для обработки огромных массивов информации в промышленных масштабах, но благодаря доступности и быстродействию оккупировала Всемирную паутину, заслужив звание «СУБД всея интернета». И сегодня MySQL всё ещё самая удобная СУБД для работы с интернет-страницами и веб-приложениями.

MySQL пользуется мощной поддержкой у создателей языков программирования: практически во всех популярных языках есть интерфейс для работы с ней.

SQLite

Эта СУБД использует большую часть стандартного языка SQL.

Главное преимущество SQlight — встраиваемость. Это объясняется тем, что SQlight не приложение типа «клиент-сервер» (в отличие от других реляционных СУБД), а библиотека, которую подключают непосредственно к программе.

И она тоже весьма популярна: достаточно сказать, что SQLite есть в каждом смартфоне. Например, в смартфонах на Android там хранятся контакты и медиа, а в iOS её используют многие приложения.

PostgreSQL

Её можно назвать самой продвинутой. Это не просто реляционная, а объектно-реляционная свободная СУБД.

PostgreSQL поддерживает не только типы данных, которые есть в других реляционных СУБД. Помимо числовых, текстовых, булевых и других стандартных типов, в ней можно хранить и обрабатывать геометрические и денежные данные, сетевые адреса, JSON, XML, массивы, а также создавать собственные типы данных.

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

Перед началомBefore You Begin

ОграниченияLimitations and Restrictions

• В таблице возможно наличие только одного ограничения по первичному ключу.A table can contain only one PRIMARY KEY constraint.

• Все столбцы с ограничением PRIMARY KEY должны иметь признак NOT NULL.All columns defined within a PRIMARY KEY constraint must be defined as NOT NULL. Если допустимость значения NULL не указана, то для всех столбцов c ограничением PRIMARY KEY устанавливается признак NOT NULL.If nullability is not specified, all columns participating in a PRIMARY KEY constraint have their nullability set to NOT NULL.

PermissionsPermissions

Создание новой таблицы с первичным ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.Creating a new table with a primary key requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

Создание первичного ключа в существующей таблице требует разрешения ALTER на таблицу.Creating a primary key in an existing table requires ALTER permission on the table.

Естественный и суррогатный ключ

Как определяют первичный ключ таблицы базы данных? Два рассмотренных нами примера – «Студенты» и «Клиенты банка» — иллюстрируют понятия естественного и суррогатного ключа. В таблице клиентов банка мы определили ключ, состоящий из полей «Номер» и «Серия паспорта», использовав уже имеющиеся столбцы. Такой ключ называется естественным, для его определения мы не производили никаких изменений и дополнений. В случае с отношением «Студенты» ни одно поле или сочетание полей не давали нам уникальности. Это вынудило нас ввести дополнительное поле с кодом учащегося. Такой ключ называется суррогатным, для него мы добавили еще один служебный столбец в таблицу. Этот столбец не несет никакой полезной информации и служит только для идентификации записей.

Создание файла базы данных

   При запуске Access открывается диалоговое окно — Окно запуска, в котором
предлагается создать новую БД, запустить Мастера БД или открыть существую­щую
БД.

   В Access поддерживаются два способа создания БД.
Можно создать пустой файл БД, а затем разрабатывать таблицы, формы, отчеты и
другие объекты, добав­ляя их в БД. Такой способ является профессиональным и
наиболее гибким, но тре­бует отдельного определения каждого элемента БД. При
выборе такого способа создания БД надо в окне запуска установить флажок Новая
база данных. В рас­крывшемся окне Файл новой базы данных следует
выбрать каталог и задать имя создаваемой БД. Раскроется Окно базы данных.

Вниманию студентов! Студенческие БД должны создаваться в
директории StudentGRNNN.

   Для создания БД можно восполь­зоваться Мастером
базы данных, уста­новив в окне запуска флажок Мастера, страницы и
проекты. Мастер создает БД, содержащую все необходимые объ­екты, и
остается только ввести в табли­цы данные. Это простейший способ на­чального
создания БД, но в этом случае придется пользоваться шаблоном, пред­лагающим
определенную структуру БД. Мастера баз данных нельзя использо­вать для
добавления новых таблиц, форм, отчетов в уже существующую БД.

   Флажок  Открыть базу данных окна
запуска позволяет открыть ранее

созданную БД, выбрав ее имя из
предлагаемого списка. При выборе Другие файлы предоставляется
каталог, из которого можно открыть нужную БД.

Языки манипулирования данными

Основное средство для общения с реляционными базами данных — язык структурированных запросов SQL.

Это декларативный язык. То есть инструкции в нём не идут одна за другой (не как в императивных языках). Каждый оператор SQL описывает только необходимое действие, а СУБД сама принимает решение, как его выполнить.

Например, чтобы выбрать все данные из таблицы Messages за 10.11.2020, делается запрос:

SELECT * FROM messages WHERE date = ‘10.11.2020’

Язык структурированных запросов делится на несколько частей (группы операторов) и позволяет:

• определять данные (DDL),
• манипулировать ими (DML),
• контролировать доступ к данным (DCL)
• и управлять транзакциями (TCL).

В SQL изначально нет средств для создания печатных отчётов, экранных форм и других инструментов для разработки программ. Хотя SQL сам по себе не является полноценным (Тьюринг-полным) языком программирования, но его стандарт позволяет создавать процедурные расширения. Они доводят его функциональность до полноценного языка программирования.

При этом синтаксис SQL в разных СУБД может различаться. Кое-где даже используются его отдельные диалекты, например:

• T-SQL — для работы с Microsoft SQL Server;
• на PL / SQL пишут хранимые процедуры и функции в Oracle;
• на PL / pgSQL — в PostgreSQL.

лМБУУЙЖЙЛБГЙС

рТПУФЩЕ Й УПУФБЧОЩЕ ЛМАЮЙ

рЕТЧЙЮОЩК ЛМАЮ НПЦЕФ УПУФПСФШ ЙЪ ЕДЙОУФЧЕООПЗП РПМС ФБВМЙГЩ, ЪОБЮЕОЙС ЛПФПТПЗП ХОЙЛБМШОЩ ДМС ЛБЦДПК ЪБРЙУЙ. фБЛ, ОБРТЙНЕТ, ОБ РТЕДРТЙСФЙЙ ОЕ НПЦЕФ ВЩФШ ДЧХИ ТБВПФОЙЛПЧ У ПДЙОБЛПЧЩНЙ ФБВЕМШОЩНЙ ОПНЕТБНЙ, РПЬФПНХ Ч ФБВМЙГЕ, УПДЕТЦБЭЕК ЪБРЙУЙ П ТБВПФОЙЛБИ, ФБВЕМШОЩК ОПНЕТ НПЦЕФ ВЩФШ РЕТЧЙЮОЩН ЛМАЮПН. фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ РТПУФЩН ЛМАЮПН.

еУМЙ ФБВМЙГБ ОЕ ЙНЕЕФ ЕДЙОУФЧЕООПЗП ХОЙЛБМШОПЗП РПМС, РЕТЧЙЮОЩК ЛМАЮ НПЦЕФ ВЩФШ УПУФБЧМЕО ЙЪ ОЕУЛПМШЛЙИ РПМЕК, УПЧПЛХРОПУФШ ЪОБЮЕОЙК ЛПФПТЩИ ЗБТБОФЙТХЕФ ХОЙЛБМШОПУФШ. фБЛ, ЙНС, ЖБНЙМЙС, ПФЮЕУФЧП, ОПНЕТ РБУРПТФБ, УЕТЙС РБУРПТФБ ОЕ НПЗХФ ВЩФШ РЕТЧЙЮОЩНЙ ЛМАЮБНЙ РП ПФДЕМШОПУФЙ, ФБЛ ЛБЛ НПЗХФ ПЛБЪБФШУС ПДЙОБЛПЧЩНЙ Х ДЧХИ Й ВПМЕЕ МАДЕК. оП ОЕ ВЩЧБЕФ ДЧХИ МЙЮОЩИ ДПЛХНЕОФПЧ ПДОПЗП ФЙРБ У ПДЙОБЛПЧЩНЙ УЕТЙЕК Й ОПНЕТПН. рПЬФПНХ Ч ФБВМЙГЕ, УПДЕТЦБЭЕК ЪБРЙУЙ П МАДСИ, РЕТЧЙЮОЩН ЛМАЮПН НПЦЕФ ВЩФШ ОБВПТ РПМЕК, УПУФПСЭЙК ЙЪ ФЙРБ МЙЮОПЗП ДПЛХНЕОФБ, ЕЗП УЕТЙЙ Й ОПНЕТБ. фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ УПУФБЧОЩН ЛМАЮПН (БОЗМ. compound key, composite key, concatenated key).

еУФЕУФЧЕООЩЕ Й УХТТПЗБФОЩЕ ЛМАЮЙ

рЕТЧЙЮОЩК ЛМАЮ НПЦЕФ УПУФПСФШ ЙЪ ЙОЖПТНБГЙПООЩИ РПМЕК ФБВМЙГЩ (ФП ЕУФШ РПМЕК, УПДЕТЦБЭЙИ РПМЕЪОХА ЙОЖПТНБГЙА ПВ ПРЙУЩЧБЕНЩИ ПВЯЕЛФБИ). фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ ЕУФЕУФЧЕООЩН ЛМАЮПН. фЕПТЕФЙЮЕУЛЙ, ЕУФЕУФЧЕООЩК ЛМАЮ ЧУЕЗДБ НПЦОП УЖПТНЙТПЧБФШ, Ч ЬФПН УМХЮБЕ НЩ РПМХЮЙН Ф. О. ЙОФЕММЕЛФХБМШОЩК ЛМАЮ. оБ РТБЛФЙЛЕ, ПДОБЛП, ЙУРПМШЪПЧБОЙЕ ЕУФЕУФЧЕООЩИ ЛМАЮЕК ОБФБМЛЙЧБЕФУС ОБ ПРТЕДЕМЈООЩЕ УМПЦОПУФЙ:

• оЙЪЛБС ЬЖЖЕЛФЙЧОПУФШ — еУФЕУФЧЕООЩК ЛМАЮ НПЦЕФ ВЩФШ ЧЕМЙЛ РП ТБЪНЕТХ (ПУПВЕООП ЛПЗДБ ПО УПУФБЧОПК), Й ЕЗП ЙУРПМШЪПЧБОЙЕ ПЛБЦЕФУС ФЕИОЙЮЕУЛЙ ОЕЬЖЖЕЛФЙЧОЩН (ЧЕДШ ЧП ЧУЕИ ФБВМЙГБИ, УЧСЪБООЩИ У ДБООПК, РПОБДПВЙФУС УПЪДБФШ РПМЕ ФПЗП ЦЕ ТБЪНЕТБ, ЮФПВЩ ИТБОЙФШ УУЩМЛЙ).
• оЕПВИПДЙНПУФШ ЛБУЛБДОЩИ ЙЪНЕОЕОЙК — рТЙ ЙЪНЕОЕОЙЙ ЪОБЮЕОЙС РПМС, ЧИПДСЭЕЗП Ч ЕУФЕУФЧЕООЩК ЛМАЮ, ПЛБЪЩЧБЕФУС ОЕПВИПДЙНЩН ЙЪНЕОЙФШ ЪОБЮЕОЙЕ РПМС ОЕ ФПМШЛП Ч ДБООПК ФБВМЙГЕ, ОП Й ЧП ЧУЕИ ФБВМЙГБИ, УЧСЪБООЩИ У ДБООПК, Ч РТПФЙЧОПН УМХЮБЕ ЧУЕ УУЩМЛЙ ОБ ДБООХА ЪБРЙУШ ПЛБЦХФУС ОЕЛПТТЕЛФОЩНЙ. ч УМПЦОЩИ ВБЪБИ ДБООЩИ ФБЛЙИ УЧСЪБООЩИ ФБВМЙГ НПЦЕФ ВЩФШ ПЮЕОШ НОПЗП, Й ЧУЕЗДБ ПУФБЈФУС ПРБУОПУФШ ХРХУФЙФШ ЙЪ ЧЙДХ ЛБЛХА-ФП ЙЪ ОЙИ. рТЙ ДПВБЧМЕОЙЙ ОПЧЩИ УЧСЪБООЩИ ФБВМЙГ РТЙИПДЙФУС ДПВБЧМСФШ УПЗМБУХАЭЙЕ ЙЪНЕОЕОЙС ЧП ЧУЕ НЕУФБ РТПЗТБНН, ЗДЕ РТБЧЙФУС ЙУИПДОБС ФБВМЙГБ.
• оЕУППФЧЕФУФЧЙЕ ТЕБМШОПУФЙ — хОЙЛБМШОПУФШ ЕУФЕУФЧЕООПЗП РЕТЧЙЮОПЗП ЛМАЮБ Ч ТЕБМШОЩИ вд ОЕ ЧУЕЗДБ УПВМАДБЕФУС. дПРХУФЙН, ОБРТЙНЕТ, ЮФП РЕТЧЙЮОЩК ЛМАЮ Ч ФБВМЙГЕ — ДБООЩЕ МЙЮОПЗП ДПЛХНЕОФБ. ч ФБЛХА ФБВМЙГХ ПЛБЦЕФУС ОЕЧПЪНПЦОЩН ЧОЕУФЙ ЮЕМПЧЕЛБ, П ДПЛХНЕОФБИ ЛПФПТПЗП ОЕФ ЙОЖПТНБГЙЙ Ч НПНЕОФ ДПВБЧМЕОЙС ЪБРЙУЙ, Б ОБ РТБЛФЙЛЕ ФБЛБС ОЕПВИПДЙНПУФШ НПЦЕФ ЧПЪОЙЛОХФШ.

чУМЕДУФЧЙЕ ЬФЙИ Й ДТХЗЙИ УППВТБЦЕОЙК Ч РТБЛФЙЛЕ РТПЕЛФЙТПЧБОЙС вд ЮБЭЕ ЙУРПМШЪХАФ Ф. О. УЙОФЕФЙЮЕУЛЙЕ (УХТТПЗБФОЩЕ) ЛМАЮЙ — ЙУЛХУУФЧЕООП УПЪДБООЩЕ ФЕИОЙЮЕУЛЙЕ ЛМАЮЕЧЩЕ РПМС, ОЕ ОЕУХЭЙЕ ЙОЖПТНБГЙЙ ПВ ПВЯЕЛФБИ.

нБФЕТЙБМ ЙЪ ЧЙЛЙРЕДЙЙ — УЧПВПДОПК ЬОГЙЛМПРЕДЙЙ
тБЪДЕМ: фЕПТЕФЙЮЕУЛЙЕ ПУОПЧЩ ВБЪ ДБООЩИ • ухвд

Что представляет собой БД?

Как известно, база данных представляет собой инструмент сбора и структурирования информации. В БД можно хранить данные о людях, заказах, товарах и т. п. Многие БД изначально выглядят как небольшой список в текстовом редакторе либо электронной таблице. Но в связи с увеличением объёма данных, список наполняется лишней информацией, появляются несоответствия, не всё становится понятным… Кроме того, способы поиска и отображения подмножеств данных при использовании обычной электронной таблицы крайне ограничены. Таким образом, лучше заранее подумать о переносе информации в базу данных, созданную в рамках системы управления БД, например, в такую, как Access.

База данных Access — это хранилище объектов. В одной такой базе данных может содержаться более одной таблицы. Представьте систему отслеживания складских запасов с тремя таблицами — это будет одна база данных, а не 3.

Что касается БД Access, то в ней все таблицы сохраняются в одном файле совместно с другими объектами (формами, отчётами, модулями, макросами).

Для файлов БД, созданных в формате Access 2007 (он совместим с Access 2010, Access 2013 и Access 2016), применяется расширение ACCDB, а для БД, которые созданы в более ранних версиях, — MDB. При этом посредством Access 2007, Access 2013, Access 2010 и Access 2016 вы сможете, при необходимости, создавать файлы и в форматах более ранних версий (Access 2000, Access 2002–2003).

Применение БД Access позволяет:
• добавлять новые данные в БД (допустим, новый артикул складских запасов);
• менять информацию, находящуюся в базе (перемещать артикул);
• удалять данные (например, когда артикул продан либо утилизирован);
• упорядочивать и просматривать данные разными методами;
• обмениваться информацией с другими людьми посредством отчётов, сообщений, эл. почты, глобальной или внутренней сети.

Первичный и уникальный ключи

Скрыть рекламу в статье

Первичный и уникальный ключи

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

В данном случае «человек» представлен одной записью в таблице, поэтому можно задаться более общим вопросом — как отличить одну запись в (любой) таблице от другой записи в этой же таблице. Для этого используются ограничения — первичные кпочи. Первичный ключ представляет собой одно или несколько полей в таблице, сочетание которых уникально для каждой записи. Для одной таблицы не существует повторяющихся значений первичного ключа.

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

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

<pkukconstraint> = {PRIMARY KEY |

UNIQUE}

Примеры первичных и уникальных ключей:

CREATE TABLE pkuk(

pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*первичный ключ*/

ukl VARCHAR(SO) NOT NULL UNIQUE,/*уникальный ключ */

uk2 INTEGER NOT NULL UNIQUE /* еще уникальный ключ */);

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

<pkuktconstraint> = {PRIMARY KEY |

UNIQUE) ( col )

Такой синтаксис позволяет создавать ключи на основе комбинации полей. Вот примеры создания первичных и уникальных ключей из нескольких полей:

CREATE TABLE pkuk2(

Number1 INTEGER NOT NULL,

Namel VARCHAR(SO) NOT NULL,

Kol INTEGER NOT NULL,

Stoim NUMERIC(15,4) NOT NULL,

CONSTRAINT pkt PRIMARY KEY (Numberl, Namel), /*первичный ключ pkt на

основе двух полей*/

CONSTRAINT uktl UNIQUE (kol, Stoim) ); /*уникальный ключ uktl на основе

двух полей*/

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

Для этого используется предложение DDL: ALTER TABLE. Синтаксис добавтения ограничений первичного или уникального ключа в существующую таблицу аналогичен описанному выше:

Помимо создания ограничения первичных и уникальных ключей в момент создания таблицы имеется возможность добавлять ограничения в уже существующую таблицу. Для этого используется предложение DDL: ALTER TABLE. Синтаксис добавтения ограничений первичного или уникального ключа в существующую таблицу аналогичен описанному выше:

ALTER TABLE tablename

ADD {PRIMARY KEY | UNIQUE) ( col )

Давайте рассмотрим пример создания первичного и уникального ключа с помощью ALTER TABLE. Сначала создаем таблицу:

CREATE TABLE pkalter(

ID1 INTEGER NOT NULL,

ID2 INTEGER NOT NULL,

UID VARCHAR(24));

Затем добавляем ключи. Сначала первичный:

ALTER TABLE pkalter

ADD CONSTRAINT pkall PRIMARY KEY (idl, id2);

Затем уникальный ключ:

ALTER TABLE pkalter

ADD CONSTRAINT ukal UNIQUE (uid) ;

Важно отметить, что добавление (а также удаление) ограничений первичных и уникальных ключей к таблице может производить только владелец этой таблицы или системный администратор SYSDBA (подробнее о владельцах и пользователе SYSDBA см. главу «Безопасность в InterBase: пользователи, роли и права») (ч

4).

Оглавление книги

Завершение первичного проекта

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

Может случиться так, что каждый продукт будет иметь несколько штрих-кодов, потому что если производитель вносит значительные из­менения в упаковку продукта, он также часто меняет и штрих-код. Например, все наверняка видели упаковки с надписью «+20% бес­платно», стоимость такой (например) бутылки лимонада не меняется, но зато благодаря этой рекламной акции лимонада вы получаете боль­ше. Обычно в таких случаях производители изменяют штрих-код, но сам продукт, по существу, не меняется. Возникает отношение: мно­жество штрих-кодов для одного продукта. Добавляем таблицу для хранения штрих-кодов (рис. 5):

Рис. 5. Добавление в базу данных таблицы BARCODE

Заметьте, что стрелка идет в направлении от таблицы BARCODE к табли­це ITEM, потому что именно штрих-кодов может быть несколько для од­ного товара

Обратите внимание на то, что barcode_ean является пер­вичным ключом, т. к

для каждого штрих-кода должна существовать уникальная строка и (хотя один продукт и может иметь несколько штрих-кодов) ни один штрих-код не может принадлежать более чем одному продукту.

Наконец, последнее добавление, которое следует внести в проект базы данных, — объем запасов каждого продукта.

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

Но может быть и так, что у нас множество товаров, при этом чаще все­го лишь какие-то из них присутствуют на складе, а объем инфор­мации, которую приходится хранить для товаров, находящихся на складе, весьма велик. Например, для склада необходимо хранить ин­формацию о размещении, номерах партий и сроках годности. Если в картотеке имеется 500 000 товаров, а на складе есть только 1000, то хранение данных по всем товарам будет просто расточительством. У этой проблемы есть стандартное решение — дополнительная таблица.

Следует создать новую таблицу, в которой хранилась бы «дополни­тельная информация» (например, сведения о хранении на складе), а затем создать только нужные строки — для продуктов, имеющихся в наличии на складе. Эти данные будут ссылаться на основную таблицу. На самом деле все гораздо проще, чем может показаться из этого объ­яснения. Окончательный проект первого варианта нашей базы дан­ных, с которым мы будем работать далее, представлен на рис. 2.19:

Рис. 2.19. Добавление в базу данных таблицы stock

Обратите внимание , что в таблице stock в качестве уникального ключа выступает , а хранящаяся в ней информация связана непосред­ственно с товарами, при этом для выполнения соединения с соответ-

ствующим товаром в таблице item используется . Стрелка ука­зывает на таблицу item, потому что это главная таблица, хотя в данном случае и нет отношения «многие-к-одному».

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

Отметьте также, что названия некоторых столбцов на рисунке под­черкнуты, это означает, что данный столбец или комбинация столбцов (например, в таблице orderinfo) гарантированно уникальны. Они обра­зуют первичный ключ таблицы.

В чем разница между кластеризованным и некластеризованным индексами в SQL?

Кластеризованный:

• Обеспечивает физический порядок по выбранному полю;
• Если у таблицы есть кластеризованный индекс, она называется кластеризованной;
• Нужно не более одного индекса на таблицу;
• В MySQL кластеризованный индекс не задается явно пользователем, так как если вы не определяете PRIMARY KEY для своей таблицы, MySQL находит первый индекс , где все ключевые столбцы — , и InnoDB использует его в качестве кластеризованного индекса.

Некластеризованный:

• В одной таблице возможно до 999 некластеризованных индексов;
• Содержит указатель на строки с реальными данными в таблице;
• Не обеспечивает физический порядок;
• Для некластеризованных индексов присутствуют отдельные таблицы с отсортированными данными, а именно — одна таблица для одного столбца, на котором индекс, поэтому при запросе данных, не входящих в состав данного поля, будет сначала выполняться запрос к полю в данной таблице, а только затем — дополнительный запрос к строке в изначальной таблице.

Виды нереляционных баз данных

Базы NoSQL делятся на четыре основные категории (в зависимости от решаемых с их помощью задач).

Ключ-значение

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

Такая СУБД не поддерживает связи между объектами, выполняет лишь операции поиска значений по ключу, добавления и удаления записи.

Например:

user1	{Кузнецов В., отдел маркетинга}
user2	{name:Лена, position:секретарь}
user3	{ООО «Вектор»}
user4	{Трофимова Таня, отд.2, дизайнер}
user5	{Галина Николаевна, гл.бух.}
user6	{65,84,236}

Базы «ключ-значение» часто используют для кэширования данных и организации очередей.

Их достоинства: быстрый поиск и простое масштабирование.

Недостатки: нельзя производить операции со значениями. Например — сортировать их или анализировать.

Одна из самых популярных — Redis. Её используют Uber, Slack, Stack Overflow, сайты гостиниц и туристические, социальная сеть Twitter.

Документоориентированные СУБД

В таких данные хранятся в виде иерархических структур (документов) с произвольным набором полей и их значений. Документы объединяются в коллекции.

Если провести аналогию с реляционными СУБД, то коллекциям соответствуют таблицы, а документам — строки в них.

Например, фрагмент документа с информацией о фильмах:

Документоориентированные базы используют в системах управления содержимым (CMS) — для хранения каталогов и пользовательских профилей.

Одна из самых популярных — MongoDB (там можно создавать процедуры на JavaScript).

Колоночные

Эти базы отличаются от реляционных лишь способом хранения данных на накопителе.

Если реляционная база создаёт для каждой таблицы по файлу, то в колоночной отдельный файл создаётся для каждого столбца таблицы.

Например, если реляционная таблица выглядит так:

name	color	property
волк	серый	зубастый
коза	белая	рогатая
капуста	зелёная

То те же записи колоночной базы будут выглядеть примерно так:

Что это даёт? Представьте, что вам нужны только названия объектов, а их свойства вас не интересуют.

При выполнении запроса в реляционной таблице просматривается каждая запись и из неё выбираются нужные данные. В колоночной базе с диска будет считана только одна колонка с названиями. Это сокращает время выполнения запроса, причём намного.

Колоночные базы применяются в различных каталогах и архивах данных, работа с которыми основана на подобных выборках.

Одна из самых популярных СУБД такого типа — Apache Cassandra.

Графовые

В некоторых предметных областях данные удобно представлять в виде графов. Для их хранения лучше всего подходят графовые базы.

Вершины (или узлы графа) — это объекты (сущности), а рёбра графа — взаимосвязи между ними.