SQL для начинающих. Вывод данных на экран в SQL. Простая выборка данных из таблицы базы данных

Опубликовано: 28.03.2017

10.Внешние ключи. Определение и характеристики. Синтаксис для указания наружного ключа. Ссылочная целостность Правила наружных ключей. Расширенный синтаксис задания внеш него ключа NULL- значения. Правило целостности объектов.

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

Week 0

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

Как нам быть уверенными, что таблицы-потомки в курсе всех конфигураций в родительской таблице?

MySQL позволяет нам держать под контролем таблицы-потомки во время обновления либо удаления данных в родительской таблице при помощи подвыражений: ON UPDATE и ON DELETE.

Week 8, continued

MySQL поддерживает 5 действий, которые можно использовать в выраженияхON UPDATE и/или ON DELETE.

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

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

нескончаемого цикла.

SET NULL:если запись в родительской таблице обновлена либо удалена, а мы желай чтоб в дочерней таблице неким занчениям было присвоено NULL (естественно если поле таблицы это позволяет)

NO ACTION: смотри RESTRICT

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

то база данных не позволит изменять записи в родительской таблице. Обе командыNO ACTION и RESTRICT эквивалентны отсутствию

подвыраженийON UPDATE or ON DELETE для наружных ключей.

SET DEFAULT:На данный момент эта команда распознается парсером, но движок InnoDB никак на нее не реагирует.

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

Таким макаром, любые конфигурации в таблицах usr и product автоматом отразятся в таблице invoice, но если продукт заказан либо если у юзера есть счет - они не могут быть удалены.

Ниже представлен новый вариант запроса CREATE для таблицы invoice с наружными ключами и выражениями ON UPDATE и ON DELETE

CREATE TABLE invoice (

inv_id int AUTO_INCREMENT NOT NULL,

usr_id int NOT NULL,

prod_id int NOT NULL,

quantity int NOT NULL,

PRIMARY KEY(inv_id),

FOREIGN KEY (usr_id) REFERENCES usr(usr_id)

ON UPDATE CASCADE

ON DELETE RESTRICT,

FOREIGN KEY (prod_id) REFERENCES product(prod_id)

ON UPDATE CASCADE

ON DELETE RESTRICT

) ENGINE=InnoDB CHARACTER SET=UTF8;

Ссы́лочная це́лостность (англ. referential integrity) — нужное качество реляционной базы данных, заключающееся в отсутствии в любом её отношении внешних ключей, ссылающихся на несуществующие кортежи.

Связи меж данными, хранимыми в различных отношениях, в реляционной БД инсталлируются при помощи использования внешних ключей — для установления связи меж кортежем из отношения A с определённым кортежем отношения B в предусмотренные для этого атрибуты кортежа отношения A записывается значение первичного ключа (а в общем случае значение потенциального ключа) мотивированного кортежа отношения B. Таким макаром, всегда имеется возможность выполнить две операции:

найти, пределить, с каким кортежем в отношении B связан определённый кортеж отношения A;

отыскать все кортежи отношения A, имеющие связи с определённым кортежем отношения B.

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

Дана пара отношений A и B, связанных наружным ключом. Первичный ключ отношения B — атрибут B.key. Наружный ключ отношения A, ссылающийся на B — атрибут A.b. Ссылочная целостность для пары отношений A и B имеет место тогда, когда производится условие: для каждого кортежа отношения A существует соответственный кортеж отношения B, другими словами кортеж, у которого (B.key = A.b).

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

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

Пример:

Так, в примере реляционная БД, состоящая из таблиц Address и Street, обеспечивает хранение адресов. При всем этом основная таблица, — Address, — содержит конкретно номер дома и квартиры, а заместо имени улицы в поле Street имеет наружный ключ, ссылающийся на таблицу Street — справочник улиц. Разумеется, что настоящий адресок должен быть представлен 2-мя связанными записями в обеих нареченных таблицах, что на техническом уровне выражается в условии: для хоть какой записи таблицы Address в таблице Street должна существовать соответственная запись, другими словами запись со (Street.Key = Address.Street). Чтоб получить перечень полных адресов из таблиц таковой структуры, когда в их соблюдается ссылочная целостность, довольно применить к данным таблицам SQL-запрос:

select *

from Address, Street

where

Address.Street = Street.Key

В данном примере, но, ссылочная целостность нарушена. Две записи таблицы Address (Key = 887 и Key = 994) имеют в поле Street так именуемые «висячие» ссылки — значения, которым не соответствуют записи в таблице Street (эти ссылки показаны красноватым цветом). Из-за этого итог вышеприведённого запроса не будет содержать этих 2-ух записей — для их условие запроса не выполнится. И ещё одна запись не будет выбрана вышеприведённым запросом — запись таблицы Address с (Key = 85). Это вариант преднамеренного (и, в неких случаях, законного) нарушения ссылочной целостности — в поле наружного ключа записан NULL (показано голубым цветом). Чтоб получить перечень всех адресов, даже тех, у каких не указана улица, нужно использовать открытое соединение, в одном из вариантов синтаксиса записываемое так:

select *

from Address left outer join Street on (Address.Street = Street.Key)

Если же требуется получить перечень, не включающий записи с «висячими» ссылками, то придётся усложнить запрос:

select *

from Address left outer join Street on ((Address.Street = Street.Key) or (Address.Street is null))

Для обеспечения ссылочной целостности либо, как время от времени молвят, для поддержки наружных ключей, у юзера базы данных должна быть возможность найти набор операций по связи меж Primary Key и Foreign Key, которые ему разрешены и которые ему запрещены. Сначала такая ситуация появляется, когда нужно выполнить операцию Delete (что делать, если в главной таблице удаляется родительская запись).

В общем случае здесь могут быть последующие операции:

1. Restrict – запретить удаление из главной, если есть подчиненные.

2. Cascade – каскадно удалить все подчиненные записи.

3. Set Null – установить значение наружных ключей подчиненных записей в нулевое значение (Null – значение).

4. Set Default – установить значение весенних ключей в предопределенное, изначальное значение.

5. No Action – ничего не делать.

В Desktop по дефлоту No Action. Если рассматривать ординарную таблицу – предки и малыши – «Если погибает родитель, это не значит, что малышей тоже нужно убивать… Запретить дохнуть тоже нельзя». Какое значение установить в подчиненных записях –«детях» – в каждом контексте решает сам юзер(Set Null и т.д.). В данном случае операции Restrict и Cascade использовать нельзя.

2-ая операция тут – это Update.(что делать, если мы обновляем, меняем Primary Key в главной таблице). Появляется вопрос, что делать с Foreign Key в этом случае. В общем случае, все ранее нареченные операции для Delete тут тоже могут быть. Все зависит также от контекста – e.g. при смене наименования улицы, нужно обновить наименования в паспортах, табличках и т.д.. Но в большинстве случаев используют операцию Restrict – запретить обновление.

Любая из операций так либо по другому просит определенного кода, чтоб ее выполнить. К исключениям можно отнести только No Action. Во всех других случаях нужно наличие кода. На практике это решается, условно говоря, одним методом, но 2-мя реализациями. Употребляется триггеры, являясь процедурой, она запускается на сервере баз данных, т.е. юзер сам таковой триггер очевидно запустить не может. Такового рода триггеры могут быть сделаны на три операции – Insert (добавление), Delete,Update. Две реализации триггеров:

1. Триггер создается вручную, т.е. программер сам пишет код, при всем этом используя разные программные конструкции, учитывает все аспекты и т.д.

2. В простом случае, без учета всех тонкостей,

Новости

rss