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

Обновлено: 02.07.2024

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

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

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

Уникальный ключ

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

уникальных ключей для одной таблицы может быть несколько (вопросик на засыпку для тех, кто прочитал статью про нормализацию: правила какой нормальной формы при этом будут нарушены? ;)
уникальные ключи могут иметь значения NULL, при этом если имеется несколько строк со значениями уникального ключа NULL, такие строки согласно стандарту SQL 92 считаются различными (уникальными).

Внешний ключ

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

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

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

Ссылочная целостность

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

Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \"болтающихся в воздухе\" строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:

CASCADE - обеспечивает автоматическое выполнение в дочерней таблице тех же изменений, которые были сделаны в родительском ключе. Если родительский ключ был изменен - ON UPDATE CASCADE обеспечит точно такие же изменения внешнего ключа в дочерней таблице. Если строка родительской таблицы была удалена, ON DELETE CASCADE обеспечит удаление всех соответствующих строк дочерней таблицы.
SET NULL - при удалении строки родительской таблицы ON DELETE SET NULL установит значение NULL во всех столбцах вторичного ключа в соответствующих строках дочерней таблицы. При изменении родительского ключа ON UPDATE SET NULL установит значение NULL в соответствующих столбцах соответствующих строк (о как:) дочерней таблицы.
SET DEFAULT - работает аналогично SET NULL, только записывает в соответствующие ячейки не NULL, а значения, установленные по умолчанию.
NO ACTION (установлено по умолчанию) - при изменении родительского ключа никаких действий с внешним ключом в дочерней таблице не производится. Но если изменение значений родительского ключа приводит к нарушению ссылочной целосности (т.е. к появлению "висящих в воздухе" строк дочерней таблицы), то СУБД не даст произвести такие изменения родительской таблицы.

Ну а сейчас - от общего к частному.

Ключи и ссылочная целостность в MySQL и Oracle

Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:

NO ACTION (устанавливается по умолчанию) в более жестком, чем по стандарту SQL 92, варианте: запрещается изменение и удаление строк родительской таблицы, для которых имеются связанные строки в дочерних таблицах.
ON DELETE CASCADE.

Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.

MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.

Разработчики MySQL обещают реализовать работу с внешними ключами и поддержание ссылочной целостности в версии 5.0. Что ж, когда версия MySQL 5.0 станет стабильной - посмотрим, что там в итоге получится. Очень, очень хотелось бы, чтобы MySQL поддерживала ссылочную целостность (без ущерба для производительности:).

Содержание

Права для пользователей MySQL

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

CREATE — позволяет создавать новые базы данных и таблицы

DROP — позволяет удалять базы данных или таблицы

INSERT — позволяет добавлять строки к таблице.

UPDATE — позволяет изменять содержание строк таблиц. Не путать с ALTER, которая позволяет изменять саму структуру таблиц (количество строк/столбцов, типы столбцов).

DELETE — противоположна INSERT — позволяет удалять строки из таблицы.

ALTER — позволяет изменять структуру таблиц. Требует CREATE и INSERT привилегии.

SELECT — позволяет читать (выводит строки) таблицы, используя выборки по столбцам и/или по некоторым арифметическим и логическим критериям.

GRANT OPTION — позволяет назначить конкретные права определенному пользователю (также и отобрать). Возможно дать/отобрать только те права, которыми назначающий сам располагает.

LOCK TABLES — блокирует таблицу на время искусственного внесения в нее изменений (администрирование), чтобы данные внутри нее не могли измениться своим естественным путем (во время рабочего процесса).

REFERENCES — позволяет создавать связь между таблицами по внешнему ключу.

EVENT — дает право на создание/изменение/удаление заданий для планировщика

TRIGGER — позволяет создавать/изменять/удалять триггеры (привязываемые к определенным таблицам), которые при выполнении операций DELETE, UPDATE или INSERT совершают дополнительные действия.

INDEX — привилегия даёт право добавлять/удалять индексы к (из) таблицам. Сами индексы назначаются вручную, и дают возможность сэкономить время на поиске строк.

CREATE TEMPORARY TABLES — позволяет создавать временные таблицы на время сессии.

CREATE VIEW — позволяет создать некоторое представление в виде таблицы, которая фактически не существует как единая и содержит лишь данные других таблиц. К примеру, в этом представлении можно собрать определенные сгруппированные данные из трёх таблиц (оператором SELECT) и по факту собранные данные будут лишь ссылаться на данные этих 3-х таблиц и также будут объединением, не нуждающимся в собственной таблице.

SHOW VIEW — позволяет проверить каким запросом (из каких данных состоит) создано определенное представление, заданное с помощью CREATE VIEW

CREATE ROUTINE — позволяет создать процедуру, которая является набором заготовленным набором SQL-команд.

ALTER ROUTINE — позволяет изменить процедуру, созданную посредством CREATE ROUTINE.

EXECUTE — позволяет вызывать готовые процедуры.

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

CREATE TABLESPACE (admin) — позволяет создавать/изменять/удалять пространства таблиц. Само это пространство является логическим и не связано со структурой БД или схемой. Оно декларирует расположение объектов БД на физических носителях и используется для оптимизации системы БД.

CREATE USER (admin) — позволяет создавать/изменять/переименовывать/удалять пользователей баз данных.

PROCESS (admin) — разрешает доступ к информации о потоках (процессах) исполняющихся на сервере.

PROXY (admin) — позволяет войти пользователем под видом другого пользователя. Используется администратором для проверки/отладки прав доступа у необходимого пользователя.

RELOAD (admin) — разрешает использование оператора FLUSH, который чистит кеш MySQL

REPLICATION CLIENT (admin) — позволяет выполнять операции SHOW MASTER STATUS, SHOW SLAVE STATUS и SHOW BINARY LOG.

REPLICATION SLAVE (admin) — данная привилегия необходима пользователям ведомого сервера БД, чтобы этот сервер мог подключаться к ведущему серверу в роли ведомого. Без этой привилегии ведомые сервера не смогут запрашивать обновления баз данных и таблиц у ведущего сервера.

SHOW DATABASES (admin) — позволяет выполнять оператор SHOW DATABASES. Пользователи, не имеющие подобной привилегии, при выполнении данного оператора смогут лишь увидеть базы данных к которым у них есть какие-либо права.

SHUTDOWN (admin) — привилегия позволяет выполнить оператор SHUTDOWN, выключающий MySQL сервер.

SUPER (admin) — привилегия, дающая право на множество операций:

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

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

Назначение прав для пользователей MySQL в панелях управления хостингом

Рассмотрим далее процедуру назначения прав пользователям MySQL в таких панелях управления хостингом, как:

DirectAdmin
cPanel
ISPmanager
Webuzo

DirectAdmin

На главной странице DirectAdmin из под уровня пользователя в меню Your Account переходим в раздел MySQL Management:

Далее нам необходимо перейти в конкретную базу данных, чтобы управлять пользователями, имеющими доступ к ней или создать новую посредством ссылки Create new Database:

Тут мы можем как создать нового пользователя для данной базы путем перехода по Create New Database User, так и привязать к ней существующего,. Следует отметить, что нет специально отведенного интерфейса для управления пользователями. Он доступен только посредством перехода через какую-либо базу данных. Чтобы дать пользователю права — переходим по ссылке modify privileges:

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

После этого произойдет переход на страницу подтверждения сохранения. Всё, права выданы.

cPanel

На главной странице cPanel нам необходимо найти раздел Базы данных в нем перейти по Базы данных MySQL:

Все манипуляции с базами данных MySQL, пользователями БД и их правами производятся именно в этом меню.

Если у нас нет ни базы, ни пользователя, то создаем их в соответствующих разделах страницы:

Раздел Текущие базы данных обновится:

Раздел Текущие пользователи обновится:

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

После добавления пользователя к базе данных откроется диалоговое окно для назначения привилегий:

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

Готово. Пользователь назначен базе данных.

ISPmanager Lite 5

При входе в ISPmanager в роли какого-либо пользователя необходимо перейти в Инструменты -> Базы данных из левого меню.

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

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

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

После этого мы увидим все права, которые можно назначить этому пользователю:

По-умолчанию при создании пользователя и БД назначаются все права. Изменяем те, которые нам необходимо и сохраняем результаты. После этого Вас вернет на страницу управления пользователями баз данных.

Webuzo

Webuzo состоит из 2-х панелей: администраторская и пользовательская. Переходим в пользовательскую панель и на главной странице выбираем Manage Databases

На открывшейся странице мы можем:

увидеть список существующих баз данных [Database(s)];
создать новую базу данных [Create Database];
увидеть список существующих пользователей баз данных [Database User(s)];
создать пользователя баз данных и назначить его определенной базе данных [Add User To Database]

Если целевой базы данных пока что не существует, то переходим в Create Database и создаем новую базу данных:

Если все же целевая база данных уже существует, то в управлении базами данных нам необходимо перейти в Add User To Database и создать нового пользователя БД или указать какого-либо существующего для его привязки к базе данных:

При добавлении пользователя к базе данных откроется новое окно с запросом назначения прав этому пользователю. Выбираем необходимые нам права и подтверждаем изменения кнопкой Submit Changes.

При успешном изменении прав в текущем окне появится надпись Database Privileges Updated. Задача выполнена.

В реляционном моделировании и реализации базы данных уникальный ключ (также известный как ключ-кандидат или просто ключ ) представляет собой набор атрибутов (столбцов) в таблице реляционной базы данных (также называемой отношением ), например:

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

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

СОДЕРЖАНИЕ

Резюме

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

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

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

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

Имя ключа	Определение
Простой	Ключ состоит только из одного атрибута.
Конкатенированный	Ключ, состоящий из нескольких атрибутов, объединенных в один ключ, например, части или всего имени с добавленным номером, сгенерированным системой, как это часто бывает для адресов электронной почты .
Сложный	Ключ состоит как минимум из двух атрибутов или простых ключей, только простые ключи существуют в составном ключе.
Композитный	Подобно составному ключу, но отдельные атрибуты не обязательно должны быть простыми ключами.
Естественный	Ключ, созданный на основе данных, существующих вне текущей базы данных. Другими словами, данные не генерируются системой, например, номер социального страхования, импортированный из другой системы.
Суррогат	Искусственный ключ, созданный из данных, которые назначаются системой или генерируются, когда существует другой ключ-кандидат. Суррогатные ключи обычно представляют собой числовые значения идентификатора и часто используются по соображениям производительности.
Кандидат	Ключ, который может стать первичным ключом.
Начальный	Ключ, выбранный в качестве первичного ключа. Только один ключ внутри объекта выбирается в качестве первичного ключа. Это ключ, которому разрешено переходить к другим объектам для определения отношений, существующих между объектами. Когда модель данных создается в физической базе данных, это ключ, который система использует больше всего при доступе к таблице или объединении таблиц вместе при выборе данных.
Альтернативный	Непервичный ключ, который можно использовать для идентификации только одной строки в таблице. Альтернативные ключи могут использоваться как первичный ключ при выборе одной таблицы.
Иностранный	Ключ, перенесенный на другой объект.

Вот пример того, как первичный ключ становится внешним ключом в связанной таблице. Идентификатор переносится из таблицы "Автор" в таблицу "Книга".

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

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

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

Определение ключей в SQL

Определение ключей в SQL:

Точно так же ключи могут быть определены как часть CREATE TABLE оператора SQL.