Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
\d flights_flight_id_seq В базе данных есть еще одна таблица, не имеющая внешних ключей, — «Бронирова- ния» (bookings) . Это довольно простая таблица. В ней всего три атрибута. Атрибут «Номер бронирования» (book_ref) является первичным ключом. Поскольку он пред- ставляет собой шестизначную комбинацию латинских букв и цифр, то в качестве ти- па данных для него выбран тип character (сокращенно — char). Для атрибута «Дата бронирования» (book_date) выбран тип данных timestamptz — временная отметка с часовым поясом, т. к. билеты могут приобретаться в городах, находящихся в различ- ных часовых поясах. В главе 4 мы уже говорили о том, что в случаях, требующих точных вычислений, необходимо использовать числа с фиксированной точностью. Работа с денежными суммами как раз и является одним из таких случаев. Поэтому для атрибута «Полная сумма бронирования» (total_amount) выбирается тип данных numeric, при этом масштаб, т. е. число цифр справа от десятичной точки (запятой), будет равен 2. CREATE TABLE bookings ( book_ref char( 6 ) NOT NULL, -- Номер бронирования book_date timestamptz NOT NULL, -- Дата бронирования total_amount numeric( 10, 2 ) NOT NULL, -- Полная стоимость -- бронирования PRIMARY KEY ( book_ref ) ); С таблицей «Бронирования» (bookings) по внешнему ключу связана таблица «Би- леты» (tickets) . В качестве первичного ключа служит атрибут «Номер билета» (ticket_no). Хотя уникальные тринадцатизначные номера билетов — числовые, но в них могут присутствовать лидирующие нули, поэтому числовой тип данных здесь не годится, а приходится использовать тип character (сокращенно — char). В каче- стве идентификатора пассажира будет использоваться номер документа, удостове- ряющего личность, а номера таких документов могут содержать, например, лиди- рующие нули, поэтому атрибут «Идентификатор пассажира» (passenger_id) будет не числовым, а символьным — varchar. Атрибут «Имя пассажира» (passenger_name) со- держит имя и фамилию пассажира, записанные заглавными латинскими буквами, а вот отчество не используется. Тип данных, конечно, text. Очень интересный атрибут «Контактные данные пассажира» (contact_data). Его особенность в том, что эти дан- ные могут иметь некоторую структуру, но при этом создавать дополнительные атри- буты в таблице нецелесообразно. С такими данными — их называют полуструктури- рованными — PostgreSQL хорошо умеет работать: для них предусмотрены типы json и jsonb. В нашей таблице используется тип jsonb и вот почему: хотя ввод данных та- кого типа несколько замедляется из-за необходимости выполнения разбора данных, 84 но этот разбор выполняется однократно, только при вводе, а последующая обработ- ка уже разобранных данных ускоряется. Подробно типы json и jsonb рассмотрены в главе 4. Внешним ключом будет атрибут «Номер бронирования» (book_ref), поскольку в рам- ках каждой процедуры бронирования может быть оформлено более одного билета. CREATE TABLE tickets ( ticket_no char( 13 ) NOT NULL, -- Номер билета book_ref char( 6 ) NOT NULL, -- Номер бронирования passenger_id varchar( 20 ) NOT NULL, -- Идентификатор пассажира passenger_name text NOT NULL, -- Имя пассажира contact_data jsonb, -- Контактные данные пассажира PRIMARY KEY ( ticket_no ), FOREIGN KEY ( book_ref ) REFERENCES bookings ( book_ref ) ); Информация обо всех перелетах сохраняется в таблице «Перелеты» (ticket_flights). Перелет — это перемещение конкретного пассажира из одного города в другой на конкретном авиарейсе. Перелеты вписываются в электронные билеты, при этом в каждый электронный билет может быть вписано более одного перелета. Поэтому первичным ключом будет комбинация двух атрибутов: «Номер билета» (ticket_no) и «Идентификатор рейса» (flight_id). С каждым перелетом связан класс обслужива- ния, значения этого атрибута подлежат проверке с помощью ограничения CHECK. Точно такое же ограничение есть и в таблице «Места» (seats), в которой каждому ме- сту в салоне конкретного типа самолета присваивается определенный класс обслу- живания. Атрибут «Стоимость перелета» (amount) требует использования типа дан- ных numeric, поскольку, как мы уже говорили ранее, денежные суммы должны запи- сываться с определенной точностью, а гарантировать ее может только тип данных numeric. Число цифр после запятой принимается равным двум. Оба атрибута, составляющих первичный ключ, в свою очередь, сами являются внеш- ними ключами. CREATE TABLE ticket_flights ( ticket_no char( 13 ) NOT NULL, -- Номер билета flight_id integer NOT NULL, -- Идентификатор рейса fare_conditions varchar( 10 ) NOT NULL, -- Класс обслуживания amount numeric( 10, 2 ) NOT NULL, -- Стоимость перелета CHECK ( amount >= 0 ), CHECK ( fare_conditions IN ( 'Economy', 'Comfort', 'Business' ) ), PRIMARY KEY ( ticket_no, flight_id ), FOREIGN KEY ( flight_id ) REFERENCES flights ( flight_id ), FOREIGN KEY ( ticket_no ) REFERENCES tickets ( ticket_no ) ); Последняя таблица нашей базы — это «Посадочные талоны» (boarding_passes). Все атрибуты, представленные в ней, за исключением атрибута «Номер посадочного та- лона» (boarding_no), вам уже известны из других таблиц. А номер посадочного тало- на — это просто целое число, порядковый номер пассажира при регистрации билетов на конкретный рейс, поэтому тип данных выбирается integer. 85 Обратите внимание, что эта таблица имеет связь с таблицей «Перелеты» (ticket_flights) типа 1:1. Это объясняется тем, что пассажир, купивший билет на конкретный рейс, при регистрации получает только один посадочный талон. Конеч- но, если пассажир на регистрацию не явился, он не получает талона. Поэтому число строк в таблице «Посадочные талоны» может в общем случае оказаться меньше числа строк в таблице «Перелеты». Логично ожидать, что первичные ключи у этих двух таблиц будут одинаковыми: они включают атрибуты «Номер билета» (ticket_no) и «Идентификатор рейса» (flight_id). Поскольку таблица «Перелеты» все же является главной в этой связке таблиц, то в таблице «Посадочные талоны» создается внешний ключ, ссылающийся на нее. А поскольку тип связи между таблицами — 1:1, то внешний ключ совпадает с первичным ключом. Известно, что номер конкретного места в самолете пассажир получает при регистра- ции билета, а не при его бронировании, поэтому атрибут «Номер места» (seat_no) на- ходится в таблице «Посадочные талоны», а не в таблице «Перелеты». Нельзя допу- стить, чтобы на одно место в салоне были направлены два и более пассажиров, по- этому создается уникальный ключ с атрибутами «Идентификатор рейса» (flight_id) и «Номер места» (seat_no). Еще один уникальный ключ призван гарантировать несов- падение номеров посадочных талонов на данном рейсе, он включает атрибуты «Идентификатор рейса» (flight_id) и «Номер посадочного талона» (boarding_no). CREATE TABLE boarding_passes ( ticket_no char( 13 ) NOT NULL, -- Номер билета flight_id integer NOT NULL, -- Идентификатор рейса boarding_no integer NOT NULL, -- Номер посадочного талона seat_no varchar( 4 ) NOT NULL, -- Номер места PRIMARY KEY ( ticket_no, flight_id ), UNIQUE ( flight_id, boarding_no ), UNIQUE ( flight_id, seat_no ), FOREIGN KEY ( ticket_no, flight_id ) REFERENCES ticket_flights ( ticket_no, flight_id ) ); Вы можете, как и раньше, посмотреть описание таблицы: \d boarding_passes В процессе создания таблиц между ними образовывались связи за счет внешних клю- чей. Эти связи в описании таблицы можно увидеть, образно говоря, с двух сторон: таблицы, на которые ссылается данная таблица, указываются во фразе «Ограничения внешнего ключа», а таблицы, которые ссылаются на данную таблицу, указываются во фразе «Ссылки извне». Например: \d tickets Ограничения внешнего ключа: "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref) Ссылки извне: TABLE "ticket_flights" CONSTRAINT "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no) 86 Наше рассмотрение команд для определения данных было бы неполным без такой важной команды, как DROP TABLE. Поскольку у вас есть файл demo_small.sql, то вос- создать таблицы базы данных будет совсем нетрудно, поэтому вы можете смело вы- полнять команды удаления таблиц. Давайте сначала попытаемся удалить таблицу aircrafts: DROP TABLE aircrafts; Казалось бы, не должно быть никаких проблем, но в результате СУБД выдает сооб- щение об ошибке: ОШИБКА: удалить объект таблица aircrafts нельзя, так как от него зависят другие объекты ,→ ПОДРОБНОСТИ: ограничение flights_aircraft_code_fkey в отношении таблица flights зависит от объекта таблица aircrafts ,→ ограничение seats_aircraft_code_fkey в отношении таблица seats зависит от объекта таблица aircrafts ,→ ПОДСКАЗКА: Для удаления зависимых объектов используйте DROP ... CASCADE. Дело в том, что таблица «Самолеты» (aircrafts) является ссылочной для таблиц «Рей- сы» (flights) и «Места» (seats), что и отражено в сообщении. Выполнив команду \d flights мы увидим внешний ключ, ссылающийся на таблицу «Самолеты» (aircrafts). В сооб- щении содержится также и подсказка, рекомендующая в команду DROP TABLE доба- вить фразу CASCADE, означающую каскадное удаление зависимых объектов. Давайте так и сделаем: DROP TABLE aircrafts CASCADE; Теперь удаление таблицы прошло успешно, при этом из таблиц «Рейсы» (flights) и «Места» (seats) были удалены внешние ключи, ссылающиеся на удаленную таблицу aircrafts. Вот это сообщение: ЗАМЕЧАНИЕ: удаление распространяется на еще 2 объекта ПОДРОБНОСТИ: удаление распространяется на объект ограничение flights_aircraft_code_fkey в отношении таблица flights удаление распространяется на объект ограничение seats_aircraft_code_fkey в отношении таблица seats ,→ ,→ ,→ DROP TABLE Теперь внешних ключей, ссылающихся на таблицу aircrafts в таблицах flights и seats нет. Можно проверить это с помощью команд \d flights \d seats А что если выполнить команду для удаления той же самой таблицы повторно? DROP TABLE aircrafts CASCADE; Ничего непоправимого не случится, просто СУБД выдаст сообщение об ошибке: ОШИБКА: таблица "aircrafts" не существует 87 Однако бывают ситуации, когда заранее известно, что возможна попытка удаления несуществующей таблицы. В таких случаях обычно стараются избежать ненужных со- общений об ошибке отсутствия таблицы. Делается это путем добавления в команду DROP TABLE фразы IF EXISTS. Например: DROP TABLE IF EXISTS aircrafts CASCADE; При использовании этой фразы в случае наличия интересующей нас таблицы выпол- няется ее удаление, в случае же ее отсутствия выводится замечание, а не ошибка, а также сообщение об успешном выполнении команды удаления таблицы: ЗАМЕЧАНИЕ: таблица "aircrafts" не существует, пропускается DROP TABLE 5.3 Модификация таблиц Модифицировать таблицы приходится по различным причинам. Например, при необходимости добавить к какому-нибудь атрибуту ограничение DEFAULT, т. е. зна- чение «по умолчанию». Конечно, если в таблицах еще нет данных, то их можно просто пересоздать, внеся изменения в их определения. Но если таблицы содержат большое количество строк, то пересоздать их не всегда возможно, в этом случае на помощь приходит команда ALTER TABLE. Эта команда очень многообразна и логична. Она предусматривает, наверное, все си- туации, которые могут возникнуть в реальной работе. Например, может возникнуть необходимость добавить новый столбец в таблицу — команда ALTER TABLE имеет для этого фразу ADD COLUMN. Возможна и обратная ситуация, когда нужно удалить стол- бец из таблицы — для этого есть фраза DROP COLUMN. Если нужно добавить ограни- чение, то помогут фразы ADD CHECK и ADD CONSTRAINT. Если потребовался внеш- ний ключ, то можно добавить и его. В качестве объектов для экспериментов будем использовать таблицы базы данных «Авиаперевозки». Предположим, что нам понадобилось иметь в базе данных сведения о крейсер- ской скорости полета всех моделей самолетов, которые эксплуатируются в нашей авиакомпании. Следовательно, необходимо добавить столбец в таблицу «Самолеты» (aircrafts). Дадим ему имя speed (наверное, можно предложить и более длинное имя — cruise_speed). Тип данных для этого столбца выберем integer, добавим ограничение NOT NULL. Наложим и ограничение на минимальное значение крейсерской скоро- сти, выраженное в километрах в час: CHECK( speed >= 300 ). В результате сформируем такую команду для добавления столбца: ALTER TABLE airports ADD COLUMN speed integer NOT NULL CHECK( speed >= 300 ); При попытке выполнить эту команду СУБД выдает сообщение об ошибке: ОШИБКА: столбец "speed" содержит значения NULL 88 Как понимать это сообщение: кто виноват и что делать? Дело в том, что в таблице «Самолеты» (aircrafts) уже есть строки. Однако во время добавления тех строк стол- бец speed в таблице не присутствовал, поэтому при его добавлении сейчас значение данного атрибута в этих строках будет отсутствовать, т. е. будет равно NULL. А мы наложили ограничение NOT NULL, следовательно, ранее добавленные строки не от- вечают новому ограничению. Как же можно выйти из этой ситуации? Один из ва- риантов такой: сначала добавить столбец, не накладывая на его значения никаких ограничений, затем ввести значения нового атрибута в уже существующие строки, причем, эти значения должны удовлетворять тем ограничениям, которые мы соби- раемся наложить. После этого накладываем все необходимые ограничения. Получаем такую группу команд: ALTER TABLE aircrafts ADD COLUMN speed integer; UPDATE aircrafts SET speed = 807 WHERE aircraft_code = '733'; UPDATE aircrafts SET speed = 851 WHERE aircraft_code = '763'; UPDATE aircrafts SET speed = 905 WHERE aircraft_code = '773'; UPDATE aircrafts SET speed = 840 WHERE aircraft_code IN ( '319', '320', '321' ); UPDATE aircrafts SET speed = 786 WHERE aircraft_code = 'CR2'; UPDATE aircrafts SET speed = 341 WHERE aircraft_code = 'CN1'; UPDATE aircrafts SET speed = 830 WHERE aircraft_code = 'SU9'; SELECT * FROM aircrafts; ALTER TABLE aircrafts ALTER COLUMN speed SET NOT NULL; ALTER TABLE aircrafts ADD CHECK( speed >= 300 ); Проверьте, как изменилось определение таблицы, с помощью команды \d aircrafts Конечно, если необходимость наличия того или иного ограничения отпадет, его мож- но удалить: ALTER TABLE aircrafts ALTER COLUMN speed DROP NOT NULL; ALTER TABLE aircrafts DROP CONSTRAINT aircrafts_speed_check; Обратите внимание, что для удаления ограничения CHECK нужно указать его имя, которое можно выяснить с помощью команды \d aircrafts Если мы решим не усложнять нашу базу данных дополнительной информацией, то можем удалить и столбец. Конечно, вовсе не обязательно предварительно удалять ограничения, наложенные на этот столбец. ALTER TABLE aircrafts DROP COLUMN speed; Еще одна полезная возможность команды ALTER TABLE — изменение типа данных для какого-либо столбца. Давайте изменим тип данных для атрибутов «Координаты аэропорта: долгота» (longitude) и «Координаты аэропорта: широта» (latitude) с float (double precision) на numeric(5, 2). Сделать это можно с помощью одной команды, по- скольку команда ALTER TABLE поддерживает и выполнение более одного действия за один раз. 89 Сначала посмотрим, с какой точностью выводятся значения этих атрибутов до изме- нения типа данных, затем изменим тип данных для двух столбцов, опять выведем содержимое таблицы на экран и убедимся, что значения были округлены в соответ- ствии с правилами округления. SELECT * FROM airports; ALTER TABLE airports ALTER COLUMN longitude SET DATA TYPE numeric( 5,2 ), ALTER COLUMN latitude SET DATA TYPE numeric( 5,2 ); SELECT * FROM airports; В том случае, когда один тип данных изменяется на другой тип данных в преде- лах одной группы, например, оба типа — числовые, то проблем обычно не возни- кает. В только что рассмотренном примере исходный тип данных был float (double precision), а новый — numeric(5, 2), поэтому операция замены типа прошла автома- тически. Однако если исходный и целевой типы данных относятся к разным группам, тогда потребуются некоторые дополнительные усилия с нашей стороны. В качестве при- мера рассмотрим следующую ситуацию. Предположим, что по результатам опытной эксплуатации базы данных «Авиаперевозки» мы пришли к выводу о том, что необхо- димо создать таблицу, содержащую коды и наименования классов обслуживания. На- зовем ее «Классы обслуживания» (fare_conditions). В ее состав включим два столбца: «Код класса обслуживания» и «Наименование класса обслуживания». Имена столбцам присвоим с учетом принципов формирования имен аналогичных столбцов в других таблицах, например, в таблице «Аэропорты» (airports). CREATE TABLE fare_conditions ( fare_conditions_code integer, fare_conditions_name varchar( 10 ) NOT NULL, PRIMARY KEY ( fare_conditions_code ) ); Добавим в новую таблицу необходимые данные: INSERT INTO fare_conditions VALUES ( 1, 'Economy' ), ( 2, 'Business' ), ( 3, 'Comfort' ); Поскольку мы ввели в обращение числовые коды для классов обслуживания, то необ- ходимо модифицировать определение таблицы «Места» (seats), а именно: тип дан- ных столбца «Класс обслуживания» (fare_conditions) изменить с varchar(10) на integer. Для реализации такой задачи служит фраза USING команды ALTER TABLE. Однако та- кой вариант команды не сработает: |