лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
 Скачать 7.88 Mb.
|
|
maker model type add_date A 1125 PC 2015-08-24 22:21:23.310 Замечания. 1. Если значение по умолчанию не указано, то подразумевается default NULL, т.е. NULL-значение. Естественно, это значение по умолчанию может быть использовано только в том случае, если на столбце нет ограничения NOT NULL. 2. Если добавить столбец в существующую таблицу, то он, согласно стандарту, будет заполнен значениями по умолчанию для имеющихся строк. В SQL Server поведение при добавлении столбца несколько отличается от стандартного. Если выполнить запрос 1. ALTER TABLE Product ADD available VARCHAR ( 20 ) DEFAULT 'Yes' ; который добавляет в таблицу Product столбец available со значением по умолчанию ‘yes’, то, как это ни странно, столбец будет заполнен NULL- значениями. Чтобы «заставить» сервер заполнить столбец значениями ‘yes’, можно использовать один из двух способов: a). Запретить NULL, т.е. написать такой запрос: 1. ALTER TABLE Product ADD available VARCHAR ( 20 ) NOT NULL DEFAULT 'Yes' ; Ясно, что этот способ не годится, если столбец допускает значения NULL. b). Использовать специальное предложение WITH VALUES: 1. ALTER TABLE Product ADD available VARCHAR ( 20 ) DEFAULT 'Yes' WITH VALUES ; Ссылочная целостность: внешний ключ (FOREIGN KEY) Внешний ключ – это ограничение, которое поддерживает согласованное состояние данных между двумя таблицами, обеспечивая так называемую ссылочную целостность. Этот тип целостности означает, что всегда есть возможность получить полную информацию об объекте, распределенную по нескольким таблицам. Причины такого распределения, связанные с принципами проектирования реляционной модели, мы рассмотрим в дальнейшем. Связь между таблицами не является равноправной. В ней всегда есть главная таблица и таблица подчиненная. Связи бывают двух типов: «один к одному» и «один ко многим». Связь «один к одному» означает, что строке главной таблицы соответствует не более одной строки (т.е. одна или ни одной) в подчиненной таблице. Связь «один ко многим» означает, что одной строке главной таблицы отвечает любое число строк (в том числе и 0) в подчиненной таблице. Связь устанавливается посредством равенства значений определенных столбцов в главной и подчиненной таблицах. При этом столбец (или набор столбцов в случае составного ключа) в подчиненной таблице, который соотносится со столбцом (или набором столбцов) в главной таблице, и называется внешним ключом. Поскольку главная таблица всегда находится со стороны «один», то столбец, участвующий в связи по внешнему ключу, должен иметь ограничение PRIMARY KEY или UNIQUE Внешний же ключ задается при создании или изменении структуры подчиненной таблицы при помощи спецификации FOREIGN KEY: 1. FOREIGN KEY ( <список столбцов 1 > REFERENCES <имя главной таблицы> ( <список столбцов 2 > ) Количество столбцов в списках 1 и 2 должно быть одинаковым, а типы данных этих столбцов должны быть попарно совместимы. Вот как можно создать внешний ключ в таблице PC: 1. ALTER TABLE PC 2. ADD CONSTRAINT fk_pc_product 3. FOREIGN KEY ( model ) REFERENCES Product ( model ) ; Замечание. Для главной таблицы можно не указывать столбец в скобках, если он является первичным ключом, т.к. он может быть только один. В нашем случае так и есть, поэтому последнюю строку можно было написать в виде 1. FOREIGN KEY ( model ) REFERENCES Product; Аналогичным образом создаются внешние ключи в таблицах Printer и Laptop. Теперь пора разобраться с тем, как работает ограничение внешнего ключа. Поскольку это ограничение поддерживает согласованность данных в двух таблицах, оно препятствует возникновению таких строк в подчиненной таблице, для которых нет соответствующих строк в главной таблице. Рассогласование могло бы возникнуть в результате выполнения следующих действий. 1. Добавление в подчиненную таблицу строки, для которой нет соответствия в главной таблице. В нашем случае внешние ключи не позволят добавить ни в одну из продукционных таблиц (PC, Laptop или Printer) изделия, модели которого нет в таблице Product. Например, мы получим ошибку при попытке выполнить такой оператор: 1. INSERT INTO pc VALUES ( 13 , 1126 , 500 , 64 , 10 , '24x' , 650 ) ; т.к. модели 1126 нет в таблице Product. The INSERT statement conflicted with the FOREIGN KEY constraint "fk_pc_product". The conflict occurred in database "learn", table "dbo.product", column 'model'. The statement has been terminated. (Конфликт инструкции INSERT с ограничением FOREIGN KEY " fk_pc_product ". Конфликт произошел в базе данных "learn", таблица "dbo.product", столбец 'model'. Выполнение данной инструкции было прервано.) 2. Изменение существующего значения внешнего ключа на значение, которого нет в соответствующем столбце главной таблицы. В нашем примере ограничение не позволит выполнить такой оператор UPDATE 1. UPDATE pc SET model = 1126 WHERE model = 1121 ; и вернёт аналогичную ошибку. 3. Удаление строки из главной таблицы, для которой есть связанные строки в подчиненной таблице. Согласованность данных здесь может поддерживаться разными способами, в соответствии со значением опции в необязательном предложении 1. ON DELETE <опция> Возможны следующие значения опции: CASCADE каскадное удаление, т.е. при удалении строки из главной таблицы будут удалены также связанные строки из подчиненной таблицы. Например, при удалении модели 1121 из таблицы Product будут удалены строки с кодами 2, 4 и 5 из таблицы PC; SET NULL - при удалении строки из главной таблицы значение внешнего ключа становится неопределенным для тех строк из подчиненной таблицы, которые связаны с удаляемой строкой. Естественно, этот вариант подразумевает, что на внешнем ключе нет ограничения NOT NULL. В нашем примере с удалением модели 1121 из таблицы Product значение столбца model в таблице PC примет значение NULL для строк с кодами 2, 4 и 5; SET DEFAULT – действие аналогичное предыдущему варианту, только вместо NULL будет использовано значение по умолчанию; NO ACTION (принимается по умолчанию) – операция выполнена не будет, если для удаляемой строки существуют связанные строки в подчиненной таблице. Если связанных строк нет, то удаление будет выполнено. Поскольку при создании внешнего ключа для таблицы PC мы не указали никакой опции, то будет использоваться NO ACTION – опция, принимаемая по умолчанию. Чтобы изменить поведение, скажем, на каскадное удаление, мы должны переписать ограничение внешнего ключа. Сделать это можно следующим образом: - удалить существующее ограничение; - создать новое ограничение. Для удаления ограничения также используется оператор ALTER TABLE: 1. ALTER TABLE <имя таблицы> 2. DROP CONSTRAINT <имя ограничения>; Вот где нам понадобилось имя ограничения! Давайте удалим внешний ключ из таблицы PC. 1. ALTER TABLE PC 2. DROP CONSTRAINT fk_pc_product; Примечание: При удалении внешнего ключа сами столбцы не удаляются, удаляется лишь ограничение. Это также справедливо и для других ограничений. Создадим теперь новое ограничение, использующее каскадное удаление: 1. ALTER TABLE PC 2. ADD CONSTRAINT fk_pc_product 3. FOREIGN KEY ( model ) REFERENCES Product ON DELETE CASCADE; 4. Изменение значений столбцов в главной таблице, с которыми связан внешний ключ в подчиненной таблице, т.е. тех столбцов, которые указаны в предложении REFERENCES ограничения FOREIGN KEY. Здесь действуют те же варианты, что и в случае с удалением строки из главной таблицы, только опция вводится предложением 1. ON UPDATE <опция> При помощи внешнего ключа, как и других ограничений, мы моделируем связи, которые существуют в предметной области. Поэтому выбор опций определяется именно предметной областью. В нашем случае при изменении номера модели в таблице Product естественно создать ограничение с опцией CASCADE, чтобы это изменение проникало в продукционные таблицы, удаляя изделия аннулированной модели, т.е. для таблицы PC нам следует написать: 1. ALTER TABLE PC 2. ADD CONSTRAINT fk_pc_product 3. FOREIGN KEY ( model ) REFERENCES Product 4. ON DELETE CASCADE 5. ON UPDATE CASCADE; Однако для другой предметной области каскадное удаление может привести к ошибочной потере данных. Пусть, например, для таблиц Сотрудники и Отделы существует связь по номеру отдела. Если при удалении (расформировании) отдела сотрудники не увольняются, а переводятся в другие отделы, то каскадное удаление ошибочно привело бы к удалению информации о сотрудниках, работавших в этом отделе. Здесь подошел бы вариант NO ACTION – чтобы сначала распределить сотрудников по другим отделам, а потом удалить «пустой» отдел; или вариант SET NULL, т.е. сначала удаляем отдел, а потом занимаемся трудоустройством сотрудников, не приписанных ни к какому отделу. Еще раз повторю, что выбор варианта зависит не от предпочтений программиста, а от процессов, имеющих место в реальном мире. Замечания 1. Между таблицами Product и PC выше мы реализовали связь «один ко многим». Связь «один к одному» создается в случае, когда в подчиненной таблице внешним ключом является уникальный столбец или уникальная комбинация столбцов. В ряде случаев связь «один к одному» является ошибкой проектирования, поскольку фактически одна сущность разбивается на две. Однако для такого разделения иногда имеются веские основания, например, когда с целью повышения производительности или обеспечения безопасности приходится выполнить вертикальное секционирование (partitioning) таблицы. 2. При удалении ограничения необходимо знать его имя. Однако, как мы уже знаем, можно создать ограничение, не давая ему имени. Как быть в этом случае? Если мы явно не указываем имя ограничения, его генерирует система. Поэтому имя всегда есть. Другой вопрос, что мы его не знаем. Тут уместно сказать, что в реляционных системах метаданные хранятся так же, как и данные, т.е. в таблицах. Стандартным представлением метаданных является информационная схема, к которой можно адресовать обычные запросы на выборку. Не углубляясь в детали, напишем запрос, который вернет нам имя ограничения внешнего ключа для таблицы PC: 1. SELECT CONSTRAINT_NAME 2. FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 3. WHERE TABLE_NAME = 'PC' AND CONSTRAINT_TYPE = 'FOREIGN KEY' ; Вложенные запросы в проверочных ограничениях Мы уже многое сделали, чтобы наша реляционная модель соответствовала предметной области. Однако некоторые проблемы, нарушающие согласованность данных, остались. Например, мы можем добавить в таблицу PC модель (1288), которая в таблице Product объявлена как принтер: 1. INSERT INTO PC VALUES ( 13 , 1288 , 500 , 64 , 10 , '24x' , 650 ) ; Более того, ничто не мешает нам добавить эту модель во все продукционные таблицы – PC, Laptop, Printer. Итак, нам требуется ограничение, которое бы запретило иметь в подчиненных таблицах продукты несоответствующего типа. Сформулируем проверочное ограничение, которое будет определять тип модели по таблице Product и сравнивать его с типом продукционной таблицы. Например, для таблицы PC такое ограничение могло бы иметь вид: 1. ALTER TABLE PC 2. ADD CONSTRAINT model_type 3. CHECK ( 'PC' = ( SELECT type FROM Product pr WHERE pr.model = pc.model )) ; При попытке выполнить вышеприведенный код (вполне легитимный с точки зрения стандарта SQL-92) получаем сообщение об ошибке: Subqueries are not allowed in this context. Only scalar expressions are allowed. (Вложенные запросы в данном контексте запрещены. Допускаются только скалярные выражения.) Другими словами, SQL Server не допускает использования подзапросов в ограничении CHECK. Что касается реализаций, то это, кстати, больше правило, чем исключение. Что касается MySQL, то эта СУБД вообще не поддерживает ограничений CHECK. Восполнить этот пробел в SQL Server позволяет использование функций пользователя (UDF). Трюк состоит в следующем. Поскольку, как это следует из сообщения об ошибке, в ограничении CHECK допускаются лишь скалярные выражения, напишем скалярнозначную функцию, которая будет принимать номер модели и возвращать ее тип, указанный в таблице Product. Затем эту функцию мы будем использовать в ограничении. Итак, 1. CREATE FUNCTION get_type ( @model VARCHAR ( 50 )) 2. RETURNS VARCHAR ( 50 ) 3. AS 4. BEGIN 5. RETURN ( SELECT type FROM Product WHERE model=@model ) 6. END ; Теперь добавим ограничение: 1. ALTER TABLE PC 2. ADD CONSTRAINT model_type CHECK ( 'PC' = dbo.get_type ( model )) ; Теперь при попытке вставить в таблицу PC модель принтера, например: 1. INSERT INTO PC VALUES ( 13 , 1288 , 500 , 64 , 10 , '24x' , 650 ) ; мы получаем следующее сообщение об ошибке: The INSERT statement conflicted with the CHECK constraint "model_type". The conflict occurred in database "learn", table "dbo.pc", column 'model'. (Конфликт инструкции INSERT с ограничением CHECK "model_type". Конфликт произошел в базе данных "learn", таблица "dbo.pc", столбец 'model'. Выполнение данной инструкции было прервано.) Модель же соответствующего типа можно добавить в таблицу: 1. INSERT INTO PC VALUES ( 13 , 1260 , 500 , 64 , 10 , '24x' , 650 ) ; Надеюсь, что вам не составит труда написать подобные ограничения и для остальных таблиц этой схемы. Проверочное ограничение уровня таблицы В описании схемы « Окраска » утверждается, что объем краски одного цвета на квадрате не может превышать 255 единиц. Как реализовать это ограничение? Рассмотренные ранее варианты нам не подойдут, т.к. каждая строка таблицы utB может отвечать всем ограничениям на отдельную окраску, но суммарный объем при этом может превысить допустимый предел. Ограничение подобного типа называется ограничением уровня таблицы, т.е. при проверке оно адресуется не к отдельной строке, которой коснулось изменение, а ко всей таблице. Поскольку тут нам опять потребуется запрос в ограничении CHECK, что не реализовано, напишем сначала пользовательскую функцию, которая будет возвращать 1, если объем какой- либо краски на каком-либо квадрате превысил 255 единиц, и ноль – в противном случае. Лежащий в основе UDF запрос достаточно прост – группировка по ИД квадрата и цвету с фильтрацией в предложении HAVING по условию, что сумма краски превысила 255. Если такой запрос будет содержать строки, то функция вернет 1, иначе – 0. Собственно функция: 1. CREATE FUNCTION check_volume () 2. RETURNS INT 3. AS 4. BEGIN 5. DECLARE @ret int 6. IF EXISTS ( SELECT SUM ( B_VOL ) 7. FROM utB JOIN utV ON b_v_id=v_id 8. GROUP BY b_q_id, V_COLOR 9. HAVING SUM ( B_VOL ) > 255 ) 10. SELECT @ret = 1 ELSE SELECT @ret = 0 ; 11. RETURN @ret; 12. END ; Осталось написать совсем простое ограничение – возвращаемое функцией значение равно 0 или не равно 1, - это кому как нравится: 1. ALTER TABLE utB 2. ADD CONSTRAINT square_volume CHECK ( dbo.check_volume () = 0 ) ; Попробуем теперь добавить какой-нибудь краски к белому квадрату (т.е. квадрату, который уже окрашен по максимуму всеми цветами), например, квадрату с b_q_id=1: 1. INSERT INTO utB VALUES ( CURRENT_TIMESTAMP, 1 , 4 , 10 ) ; В результате мы получим ошибку: The INSERT statement conflicted with the CHECK constraint "square_volume". The conflict occurred in database "learn", table "dbo.utb". The statement has been terminated. (Конфликт инструкции INSERT с ограничением CHECK "square_volume". Конфликт произошел в базе данных "learn", таблица "dbo.utb". Выполнение данной инструкции было прервано.) В качестве упражнения напишите ограничение, которое запретит использование пустых баллончиков, т.е. когда объем краски, израсходованной из баллончика, оказывается более 255. INFORMATION_SCHEMA и Oracle Информационная схема (INFORMATION_SCHEMA) является стандартным представлением метаданных в языке SQL. Исторически каждый производитель реляционных СУБД предоставлял системные таблицы, которые содержали мета- информацию - имена таблиц, столбцов, ограничений, типы данных столбцов и т.д. Структура и состав системных таблиц могут меняться в разных версиях продукта, однако поддержка информационной схемы дает возможность менять структуру системных таблиц без изменения способа доступа к метаданным. Другим преимуществом применения INFORMATION_SCHEMA является то, что запросы к метаданным не зависят от используемой СУБД. Из ведущих производителей, пожалуй, только |