alan_beaulieu-learning_sql-ru (865932), страница 45
Текст из файла (страница 45)
Индексы и ограниченияСоздание ограниченийОбычно ограничения создают одновременно с ассоциированной табли цей посредством выражения create table. Для иллюстрации приведемпример из сценария формирования схемы для БД, используемой в ка честве примера к этой книге:CREATE TABLE product(product_cd VARCHAR(10) NOT NULL,name VARCHAR(50) NOT NULL,product_type_cd VARCHAR (10) NOT NULL,date_offered DATE,date_retired DATE,CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)REFERENCES product_type (product_type_cd),CONSTRAINT pk_product PRIMARY KEY (product_cd));Таблица product включает два ограничения: первое определяет столбецproduct_cd как первичный ключ таблицы, а второе – столбец product_type_cd как внешний ключ к таблице product_type.
Альтернативный ва риант: таблицу product можно было создать без ограничений, а ограни чения первичного и внешнего ключей добавить позже посредством вы ражений alter table:ALTER TABLE productADD CONSTRAINT pk_product PRIMARY KEY (product_cd);ALTER TABLE productADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)REFERENCES product_type (product_type_cd);Если требуется убрать ограничения первичного или внешнего ключей,можно опять воспользоваться выражением alter table, только в этомслучае задается drop, а не add:ALTER TABLE productDROP PRIMARY KEY;ALTER TABLE productDROP FOREIGN KEY fk_product_type_cd;Ограничение первичного ключа обычно не удаляется, а ограничениявнешнего ключа иногда отменяются во время определенных операцийобслуживания, а потом устанавливаются вновь.Ограничения и индексыКак было показано в этой главе ранее, иногда создание ограниченийвключает автоматическое формирование индекса.
Однако серверы БДведут себя по разному в зависимости от связи между ограничениямии индексами. В табл. 13.1 показаны связи между ограничениями и ин дексами в MySQL, SQL Server и Oracle Database.253ОграниченияТаблица 13.1. Формирование ограниченийТип ограничения MySQLSQL ServerOracle DatabaseОграничениеФормирует уни первичного ключа кальный индексФормирует уни кальный индексИспользует имею щийся индекс илисоздает новыйОграничениевнешнего ключаФормирует индекс Не формируетиндексНе формируетиндексОграничениеуникальностиФормирует уни кальный индексИспользует имею щийся индекс илисоздает новыйФормирует уни кальный индексСледовательно, MySQL формирует новый индекс, чтобы реализоватьограничения первичного ключа, внешнего ключа и уникальности.
SQLServer формирует новый индекс для ограничений первичного ключаи уникальности, а для ограничений внешнего ключа – нет. Oracle Da tabase использует такой же подход, как и SQL Server, за тем исключе нием, что Oracle для введения в действие ограничений первичного клю ча и уникальности будет использовать существующий индекс (еслиесть подходящий). Хотя ни SQL Server, ни Oracle Database не формиру ют индекс для ограничения внешнего ключа, документация обоих сер веров рекомендует создавать индекс для каждого внешнего ключа.Каскадные ограниченияЕсли при наличии ограничений внешнего ключа пользователь пытает ся вставить новую строку или изменить существующую и при этом по лучается, что столбец внешнего ключа не имеет соответствующего зна чения в родительской таблице, сервер формирует ошибку. Для иллю страции рассмотрим данные таблиц product и product_type:mysql> SELECT product_type_cd, name> FROM product_type;+++| product_type_cd | name|+++| ACCOUNT| Customer Accounts|| INSURANCE| Insurance Offerings|| LOAN| Individual and Business Loans |+++3 rows in set (0.00 sec)mysql> SELECT product_type_cd, product_cd, name> FROM product> ORDER BY product_type_cd;++++| product_type_cd | product_cd | name|++++| ACCOUNT| CD| certificate of deposit || ACCOUNT| CHK| checking account|254Глава 13.
Индексы и ограничения| ACCOUNT| MM| money market account|| ACCOUNT| SAV| savings account|| LOAN| AUT| auto loan|| LOAN| BUS| business line of credit || LOAN| MRT| home mortgage|| LOAN| SBL| small business loan|++++8 rows in set (0.01 sec)В таблице product_type имеется три разных значения для столбца product_type_cd (ACCOUNT, INSURANCE и LOAN). Два из этих трех значений (ACCOUNT и LOAN) упоминаются в столбце product_type_cd таблицы product.Следующее выражение делает попытку изменить значение столбцаproduct_type_cd таблицы product на значение, которого нет в таблицеproduct_type:mysql> UPDATE product> SET product_type_cd = 'XYZ'> WHERE product_type_cd = 'LOAN';ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraintfailsИз за ограничения внешнего ключа на столбец product.product_type_cdсервер не разрешает провести такое обновление, поскольку в таблицеproduct_type в столбце product_type_cd нет строки со значением 'XYZ'. Та ким образом, ограничение внешнего ключа не позволяет изменить до чернюю строку, если в родительской нет соответствующего значения.Однако что произошло бы, попробуй мы изменить значение родительской строки таблицы product_type на 'XYZ'? Вот выражение update, реа лизующее попытку изменить тип счета LOAN на XYZ:mysql> UPDATE product_type> SET product_type_cd = 'XYZ'> WHERE product_type_cd = 'LOAN';ERROR 1217 (23000): Cannot delete or update a parent row: a foreignkey constraintfailsОпять формируется ошибка.
На этот раз потому, что в таблице productесть дочерние строки, столбец product_type_cd которых содержит зна чение 'LOAN'. Это поведение ограничений внешнего ключа по умолча нию, но оно не единственное возможное. Можно указать серверу рас пространять это изменение на все дочерние строки, сохраняя, такимобразом, целостность данных. Эта разновидность ограничения внеш него ключа, известная как каскадное обновление (cascading update),может быть установлена путем удаления существующего внешнегоключа и добавления нового, включающего блок on update cascade:mysql> ALTER TABLE product> DROP FOREIGN KEY fk_product_type_cd;Query OK, 8 rows affected (0.02 sec)Ограничения255Records: 8 Duplicates: 0 Warnings: 0mysql> ALTER TABLE product> ADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)> REFERENCES product_type (product_type_cd)> ON UPDATE CASCADE;Query OK, 8 rows affected (0.03 sec)Records: 8 Duplicates: 0 Warnings: 0Изменив ограничение таким образом, посмотрим, что произойдет, ес ли попытаться выполнить выражение update снова:mysql> UPDATE product_type> SET product_type_cd = 'XYZ'> WHERE product_type_cd = 'LOAN';Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0На этот раз выражение выполняется успешно.
Для проверки того, чтоизменения были распространены на таблицу product, еще раз посмот рим на данные в обеих таблицах:mysql> SELECT product_type_cd, name> FROM product_type;+++| product_type_cd | name|+++| ACCOUNT| Customer Accounts|| INSURANCE| Insurance Offerings|| XYZ| Individual and Business Loans |+++3 rows in set (0.02 sec)mysql> SELECT product_type_cd, product_cd, name> FROM product> ORDER BY product_type_cd;++++| product_type_cd | product_cd | name|++++| ACCOUNT| CD| certificate of deposit || ACCOUNT| CHK| checking account|| ACCOUNT| MM| money market account|| ACCOUNT| SAV| savings account|| XYZ| AUT| auto loan|| XYZ| BUS| business line of credit || XYZ| MRT| home mortgage|| XYZ| SBL| small business loan|++++8 rows in set (0.01 sec)Как видите, изменения таблицы product_type распространились и на таб лицу product.
Кроме каскадных обновлений можно задавать каскадные удаления (cascading deletes). При каскадном удалении, если стро 256Глава 13. Индексы и ограниченияка удаляется в родительской таблице, соответствующие ей строки уда ляются и в дочерней таблице. Для задания каскадного удаления ис пользуется блок on delete cascade:ALTER TABLE productADD CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd)REFERENCES product_type (product_type_cd)ON UPDATE CASCADEON DELETE CASCADE;Теперь при таком варианте ограничения сервер будет обновлять дочер ние строки таблицы product при обновлении строки в таблице product_type, а также удалять дочерние строки таблицы product при удале нии строки таблицы product_type.Каскадные ограничения – один из случаев, когда ограничения непосредственно влияют на код, который вы пишете.
Чтобы полностьюпредставлять эффект применения выражений update и delete, необхо димо знать, для каких ограничений базы данных заданы каскадныеобновления и/или удаления.ERдиаграмма примера базы данныхНа рис. А.1 представлена диаграмма сущностей и связей (entity relati onship, ER) базы данных, используемой в этой книге в качестве приме ра.
Как следует из названия, диаграмма отображает сущности, илитаблицы, базы данных и связи внешнего ключа между таблицами. Вотнесколько подсказок, которые помогут понять условные обозначения:• Каждый прямоугольник представляет таблицу. Имя таблицы ука зано в верхнем левом углу прямоугольника. Столбец (или столбцы)первичного ключа указан первым и отделен от обычных столбцовлинией.
Обычные столбцы перечислены под линией, столбцы внеш него ключа отмечены как «(FK)».• Линиями между таблицами представлены связи внешнего ключа.Отметки на концах линий показывают допустимую кратность свя зи, которая может иметь значения нуль (0), один (1) или много ( ).Например, взглянув на связь между таблицами account и product,можно сказать, что счет должен относиться только к одной услуге,но для одной услуги может быть нуль, один или много счетов.Более подробно моделирование баз данных и соответствующие инстру менты рассмотрены в приложении D.258Приложение A.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
