Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
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) требует использования типа данных numer- ic, поскольку, как мы уже говорили ранее, денежные суммы должны записываться с определенной точностью, а гарантировать ее может только тип данных numeric. Число цифр после запятой принимается равным двум. Оба атрибута, составляющих первичный ключ, в свою очередь, сами являются внеш- ними ключами. 113 Глава 5. Основы языка определения данных 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. Обратите внимание, что эта таблица имеет связь типа 1:1 с таблицей «Перелеты». Это объясняется тем, что пассажир, купивший билет на конкретный рейс, при регистра- ции получает только один посадочный талон. Конечно, если пассажир не явился на регистрацию, он не получает талона. Поэтому число строк в таблице «Посадочные талоны» может в общем случае оказаться меньше числа строк в таблице «Переле- ты». Логично ожидать, что первичные ключи у этих двух таблиц будут одинаковы- ми: они включают атрибуты «Номер билета» (ticket_no) и «Идентификатор рейса» (flight_id). Поскольку таблица «Перелеты» все же является главной в этой связке таблиц, то в таблице «Посадочные талоны» создается внешний ключ, ссылающийся на нее. А поскольку тип связи между таблицами — 1:1, то внешний ключ совпадает с первичным ключом. Известно, что номер конкретного места в самолете пассажир получает при регистра- ции билета, а не при его бронировании, поэтому атрибут «Номер места» (seat_no) находится в таблице «Посадочные талоны», а не в таблице «Перелеты». Нельзя допустить, чтобы на одно место в салоне были направлены два и более пас- сажиров, поэтому создается уникальный ключ с атрибутами «Идентификатор рейса» (flight_id) и «Номер места» (seat_no). Еще один уникальный ключ призван га- рантировать несовпадение номеров посадочных талонов на данном рейсе, он вклю- чает атрибуты «Идентификатор рейса» (flight_id) и «Номер посадочного талона» (boarding_no). 114 5.2. Создание и удаление таблиц 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) Наше рассмотрение команд для определения данных было бы неполным без такой важной команды, как DROP TABLE. Поскольку у вас есть файл demo_small.sql, то вос- создать таблицы базы данных будет совсем нетрудно, поэтому вы можете смело вы- полнять команды удаления таблиц. Давайте сначала попытаемся удалить таблицу aircrafts: DROP TABLE aircrafts; 115 Глава 5. Основы языка определения данных Казалось бы, не должно быть никаких проблем, но в результате СУБД выдает сооб- щение об ошибке: ОШИБКА: удалить объект таблица aircrafts нельзя, так как от него зависят другие объекты ПОДРОБНОСТИ: ограничение flights_aircraft_code_fkey в отношении таблица flights зависит от объекта таблица aircrafts ограничение seats_aircraft_code_fkey в отношении таблица seats зависит от объекта таблица aircrafts ПОДСКАЗКА: Для удаления зависимых объектов используйте DROP ... CASCADE. Дело в том, что таблица «Самолеты» (aircrafts) является ссылочной для таблиц «Рейсы» (flights) и «Места» (seats), что и отражено в этом сообщении. Выполнив команду \d flights мы увидим внешний ключ, ссылающийся на таблицу «Самолеты». В сообщении со- держится также и подсказка, рекомендующая в команду DROP TABLE добавить фразу CASCADE, означающую каскадное удаление зависимых объектов. Давайте так и сде- лаем: DROP TABLE aircrafts CASCADE; Теперь удаление таблицы прошло успешно, при этом из таблиц «Рейсы» и «Места» были удалены внешние ключи, ссылающиеся на удаленную таблицу 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; 116 5.3. Модификация таблиц Ничего непоправимого не случится, просто СУБД выдаст сообщение об ошибке: ОШИБКА: таблица "aircrafts" не существует Однако бывают ситуации, когда заранее известно, что возможна попытка удаления несуществующей таблицы. В таких случаях обычно стараются избежать ненужных со- общений об ошибке отсутствия таблицы. Делается это путем добавления в команду 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. Если потребовался внешний ключ, то можно добавить и его. В качестве объектов для экспериментов будем использовать таблицы базы данных «Авиаперевозки». Предположим, что нам понадобилось иметь в базе данных сведения о крейсер- ской скорости полета всех моделей самолетов, которые эксплуатируются в нашей авиакомпании. Следовательно, необходимо добавить столбец в таблицу «Самолеты» 117 Глава 5. Основы языка определения данных (aircrafts). Дадим ему имя speed (наверное, можно предложить и более длинное имя — cruise_speed). Тип данных для этого столбца выберем integer, добавим ограничение NOT NULL. Наложим ограничение и на минимальное значение крейсер- ской скорости, выраженное в километрах в час: CHECK( speed >= 300 ). В результате сформируем такую команду для добавления столбца: ALTER TABLE airports ADD COLUMN speed integer NOT NULL CHECK( speed >= 300 ); При попытке выполнить эту команду СУБД выдает сообщение об ошибке: ОШИБКА: столбец "speed" содержит значения NULL Как понимать это сообщение: кто виноват и что делать? Дело в том, что в таблице «Самолеты» уже есть строки. Однако во время добавления тех строк столбец 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 118 5.3. Модификация таблиц Конечно, если необходимость наличия того или иного ограничения отпадет, его мож- но удалить: 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 поддерживает и выполнение более одного действия за один раз. Сначала посмотрим, с какой точностью выводятся значения этих атрибутов до изме- нения типа данных, затем изменим тип данных для двух столбцов, опять выведем содержимое таблицы на экран и убедимся, что значения были округлены в соответ- ствии с правилами округления. 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), поэтому операция замены типа прошла ав- томатически. 119 Глава 5. Основы языка определения данных Однако если исходный и целевой типы данных относятся к разным группам, тогда потребуются некоторые дополнительные усилия с нашей стороны. В качестве при- мера рассмотрим следующую ситуацию. Предположим, что по результатам опытной эксплуатации базы данных «Авиаперевозки» мы пришли к выводу о том, что необ- ходимо создать таблицу, содержащую коды и наименования классов обслуживания. Назовем ее «Классы обслуживания» (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' ); Поскольку мы ввели в обращение числовые коды для классов обслуживания, то необходимо модифицировать определение таблицы «Места», а именно: тип дан- ных столбца «Класс обслуживания» (fare_conditions) изменить с varchar(10) на integer. Для реализации такой задачи служит фраза USING команды ALTER TABLE. Однако такой вариант команды не сработает: ALTER TABLE seats ALTER COLUMN fare_conditions SET DATA TYPE integer USING ( CASE WHEN fare_conditions = 'Economy' THEN 1 WHEN fare_conditions = 'Business' THEN 2 ELSE 3 END ); Для замены исходных значений на новые мы используем конструкцию CASE WHEN ... THEN ... ELSE ... END. Выполнить операцию не удастся, СУБД выдаст сообщение об ошибке: ОШИБКА: ограничение-проверку "seats_fare_conditions_check" нарушает некоторая строка 120 5.3. Модификация таблиц И в самом деле, в определении таблицы «Места» есть ограничение CHECK, которое требует, чтобы значение столбца fare_conditions выбиралось из списка: Economy, Comfort, Business. При замене символьных значений на числовые это ограничение будет заведомо нарушаться. Следовательно, необходимо в команду ALTER TABLE до- бавить операцию удаления этого ограничения. Пробуем новый вариант команды: ALTER TABLE seats DROP CONSTRAINT seats_fare_conditions_check, ALTER COLUMN fare_conditions SET DATA TYPE integer USING ( CASE WHEN fare_conditions = 'Economy' THEN 1 WHEN fare_conditions = 'Business' THEN 2 ELSE 3 END ); Проверим результат работы с помощью команды SELECT * FROM seats; aircraft_code | seat_no | fare_conditions ---------------+---------+----------------- 319 | 2A | 2 319 | 2C | 2 319 | 2D | 2 Теперь мы видим, что необходимо связать таблицы «Места» и «Классы обслужива- ния» по внешнему ключу. Сделаем это: ALTER TABLE seats ADD FOREIGN KEY ( fare_conditions ) REFERENCES fare_conditions ( fare_conditions_code ); Посмотрев описание таблицы «Места», увидим, что внешний ключ успешно создан. \d seats "seats_fare_conditions_fkey" FOREIGN KEY (fare_conditions) REFERENCES fare_conditions(fare_conditions_code) Из теории известно, что атрибуты внешнего ключа не обязательно должны ссылать- ся только на одноименные атрибуты ссылочной таблицы. Сейчас мы на практике успешно проверили это утверждение. Однако для удобства сопровождения базы дан- ных имеет смысл переименовать столбец fare_conditions в таблице «Места», т. е. 121 Глава 5. Основы языка определения данных дать ему имя fare_conditions_code, поскольку в этой таблице хранится именно код класса обслуживания. Давайте так и поступим: ALTER TABLE seats RENAME COLUMN fare_conditions TO fare_conditions_code; Если теперь посмотреть описание таблицы, то можно заметить, что имя атрибута, являющегося внешним ключом, изменилось, а вот имя ограничения осталось неиз- менным (seats_fare_conditions_fkey), хотя оно и было первоначально сформи- ровано самой СУБД. Это шаблонное имя ограничения составляется из имени таблицы и имени первого (и единственного в данном случае) атрибута внешнего ключа. "seats_fare_conditions_fkey" FOREIGN KEY (fare_conditions_code) REFERENCES fare_conditions(fare_conditions_code) Давайте переименуем это ограничение, чтобы поддержать соблюдение правила име- нования ограничений: |