ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 19.03.2024
Просмотров: 192
Скачиваний: 0
ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.
СОДЕРЖАНИЕ
5.1. Значения по умолчанию и ограничения целостности
Переходим к первичным ключам. Как мы уже говорили ранее, этот ключ является уникальным идентификатором строк в таблице. Ключ может быть как простым, т. е.
включать только один атрибут, так и составным, т. е. включать более одного атрибута.
При этом в отличие от уникального ключа, определяемого с помощью ограничения
UNIQUE, атрибуты, входящие в состав первичного ключа, не могут иметь значений
NULL. Таким образом, определение первичного ключа эквивалентно определению уникального ключа, дополненного ограничением NOT NULL. Однако не стоит в ре- альной работе заменять первичный ключ комбинацией ограничений UNIQUE и NOT
NULL, поскольку теория баз данных требует наличия в каждой таблице именно пер- вичного ключа.
Первичный ключ является частью метаданных, его наличие позволяет другим табли- цам использовать его в качестве уникального идентификатора строк в данной таб- лице. Это удобно, например, при создании внешних ключей, речь о которых пойдет ниже. Перечисленными свойствами обладает также и уникальный ключ.
Если первичный ключ состоит из одного атрибута, то можно указать его непосред- ственно в определении этого атрибута:
CREATE TABLE students
( record_book numeric( 5 ) PRIMARY KEY,
...
);
А можно сделать это и в виде отдельного ограничения:
CREATE TABLE students
( record_book numeric( 5 ),
...
PRIMARY KEY ( record_book )
);
В случае создания составного первичного ключа имена столбцов, входящих в его со- став, перечисляются в выражении PRIMARY KEY через запятую:
PRIMARY KEY ( имя-столбца1, имя-столбца2, ...)
При добавлении первичного ключа автоматически создается индекс на основе B- дерева для поддержки этого ограничения.
В таблице может быть любое число ограничений UNIQUE, дополненных ограниче- нием NOT NULL, но первичный ключ может быть только один. PostgreSQL допускает и отсутствие первичного ключа, хотя строгая теория реляционных баз данных не ре- комендует так поступать.
99
Глава 5. Основы языка определения данных
Завершаем наш обзор различных видов ограничений рассмотрением такого важно- го понятия, как внешний ключ (foreign key). Внешние ключи являются средством поддержания так называемой ссылочной целостности (referential integrity) между связанными таблицами. Напомним, что это означает, на примере таблиц «Студен- ты» (students) и «Успеваемость» (progress). В первой из них содержатся данные о студентах, а во второй — сведения об их успеваемости. Поскольку в процессе обу- чения студенты сдают целый ряд зачетов и экзаменов, то в таблице «Успеваемость»
для каждого студента может присутствовать несколько строк. Для большинства из них это так и будет, хотя, в принципе, возможна ситуация, когда для какого-то сту- дента в таблице «Успеваемость» не окажется ни одной строки (если, он, например,
находится в академическом отпуске).
Конечно, должна быть возможность определить, какому студенту принадлежат те или иные оценки, т. е. какие строки в таблице «Успеваемость» с какими строками в таблице «Студенты» связаны. Для решения этой задачи не требуется в каждой стро- ке таблицы «Успеваемость» повторять все сведения о студенте: номер зачетной книж- ки, фамилию, имя и отчество, данные документа, удостоверяющего личность. Доста- точно включить в состав каждой строки таблицы «Успеваемость» лишь уникальный идентификатор строки из таблицы «Студенты». В нашем случае это будет номер за- четной книжки — record_book. Данный атрибут и будет являться внешним ключом таблицы «Успеваемость». Таким образом, получив строку из таблицы «Студенты»,
можно будет найти все соответствующие ей строки в таблице «Успеваемость», сопо- ставив значения атрибутов record_book в строках обеих таблиц. В результате мы сможем получить все строки таблицы «Успеваемость», связанные с конкретной стро- кой из таблицы «Студенты» по внешнему ключу.
Таблица «Успеваемость» будет ссылающейся (referencing), а таблица «Студенты» —
ссылочной
(referenced). Обратите внимание, что внешний ключ ссылающейся таб- лицы ссылается на первичный ключ ссылочной таблицы. Допускается ссылка и на уникальный ключ, не являющийся первичным. В данном контексте для описания от- ношений между таблицами можно сказать, что таблица students является главной,
а таблица progress — подчиненной.
Создать внешний ключ можно в формате ограничения уровня атрибута следующим образом:
CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students ( record_book ),
...
);
100
5.1. Значения по умолчанию и ограничения целостности
Предложение REFERENCES создает ограничение ссылочной целостности и указыва- ет в качестве ссылочного ключа атрибут record_book. Это означает, что в таблицу
«Успеваемость» нельзя ввести строку, значение атрибута record_book которой от- сутствует в таблице «Студенты». Говоря простым языком, нельзя ввести запись об оценке того студента, информация о котором еще не введена в таблицу «Студенты».
Поскольку внешний ключ в нашем примере ссылается на первичный ключ, можно использовать сокращенную форму записи этого ограничения, не указывая список ат- рибутов:
CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students,
...
);
Можно определить внешний ключ и в форме ограничения уровня таблицы:
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
);
Конечно, число атрибутов и их типы данных во внешнем ключе ссылающейся табли- цы и в первичном ключе ссылочной таблицы должны быть согласованы.
Ограничению внешнего ключа можно присвоить наименование, как и любому дру- гому ограничению, с помощью ключевого слова CONSTRAINT.
При наличии связей между таблицами, организованных с помощью внешних клю- чей, необходимо придерживаться определенной политики при выполнении опера- ций удаления и обновления строк в ссылочных таблицах — тех, на которые ссылаются другие таблицы. В нашем примере ситуация принятия «политического» решения воз- никает при удалении строк из таблицы «Студенты».
Конечно, если бы было принято решение хранить всю историю успеваемости сту- дентов, в том числе и отчисленных, тогда строки из таблицы students вообще не удалялись бы. Но, упрощая реальную ситуацию, мы решили историю не хранить.
Тогда возникает закономерный вопрос: что делать со строками в таблице «Успева- емость» (progress), которые ссылаются на удаляемую строку в таблице «Студенты»
(students)?
101
Глава 5. Основы языка определения данных
Возможны несколько вариантов.
1. Удаление связанных строк из таблицы «Успеваемость», что означает, что при отчислении студента будет удаляться вся история его успехов в учебе. Эта опе- рация называется каскадным удалением, и для ее реализации в определение внешнего ключа добавляются ключевые слова ON DELETE CASCADE.
Например:
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE CASCADE
);
2. Запрет удаления строки из таблицы «Студенты», если в таблице «Успеваемость»
есть хотя бы одна строка, ссылающаяся на удаляемую строку в таблице «Сту- денты». Для реализации такой политики в определение внешнего ключа до- бавляются ключевые слова ON DELETE RESTRICT или ON DELETE NO ACTION.
Если в определении внешнего ключа не предписано конкретное действие, то по умолчанию используется NO ACTION.
Оба эти варианта означают, что если в ссылающейся таблице, т. е. «Успевае- мость», есть строки, ссылающиеся на удаляемую строку в таблице «Студенты»,
то операция удаления будет отменена, и будет выведено сообщение об ошибке.
Отличие между этими двумя вариантами лишь в том, что при использовании NO
ACTION можно отложить проверку выполнения ограничения на более поздний строк в рамках транзакции, а в случае RESTRICT проверка выполняется немед- ленно.
Поэтому если бы внешний ключ определили таким образом:
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE RESTRICT
);
102
5.1. Значения по умолчанию и ограничения целостности
или таким:
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
);
то при попытке удаления строки из таблицы «Студенты» и наличии в таблице
«Успеваемость» строк, связанных с ней, операция удаления была бы отменена с выводом сообщения об ошибке.
3. Присваивание атрибутам внешнего ключа в строках таблицы «Успеваемость»
значения NULL. Для реализации этого подхода необходимо, чтобы на атрибу- ты внешнего ключа не было наложено ограничение NOT NULL.
Оформляется этот вариант так:
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE SET NULL
);
4. Присваивание атрибутам внешнего ключа в строках таблицы «Успеваемость»
значения по умолчанию (DEFAULT), если оно, конечно, было предписано при создании таблицы.
Оформляется этот вариант так (значение во фразе DEFAULT взято произволь- ным образом):
CREATE TABLE progress
( record_book numeric( 5 ) DEFAULT 12345,
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE SET DEFAULT
);
Важно учитывать, что если в ссылочной таблице нет строки с тем же значением ключевого атрибута, которое было предписано во фразе DEFAULT при создании
103
Глава 5. Основы языка определения данных
ссылающейся таблицы, то будет иметь место нарушение ограничения ссылоч- ной целостности и операция удаления не будет выполнена.
При выполнении операции UPDATE используются эти же варианты подходов по отношению к обеспечению ссылочной целостности. Аналогом каскадного уда- ления является каскадное обновление:
1 2 3 4 5 6 7 8 9 ... 20
CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON UPDATE CASCADE
);
В случае каскадного обновления измененные значения ссылочных атрибутов копируются в ссылающиеся строки ссылающейся таблицы, т. е. новое значение атрибута record_book из строки таблицы «Студенты» будет скопировано во все строки таблицы «Успеваемость», ссылающиеся на обновленную строку.
После рассмотрения всех видов ограничений целостности, которые можно указать для базы данных, мы можем привести окончательные определения таблиц «Студен- ты» и «Успеваемость». Окончательными они являются лишь в том смысле, что имен- но их нужно брать за основу при выполнении заданий, приведенных в конце главы.
Эти определения ни в коем случае не являются идеальными, эталонными. Выполняя задания, вы это увидите сами.
Прежде чем создавать таблицы, создайте базу данных edu:
createdb -U postgres edu
Подключитесь к ней:
psql -d edu -U postgres
Создайте обе таблицы:
CREATE TABLE students
( record_book numeric( 5 ) NOT NULL,
name text NOT NULL,
doc_ser numeric( 4 ),
doc_num numeric( 6 ),
PRIMARY KEY ( record_book )
);
104
5.2. Создание и удаление таблиц
CREATE TABLE progress
( record_book numeric( 5 ) NOT NULL,
subject text NOT NULL,
acad_year text NOT NULL,
term numeric( 1 ) NOT NULL CHECK ( term = 1 OR term = 2 ),
mark numeric( 1 ) NOT NULL CHECK ( mark >= 3 AND mark <= 5 )
DEFAULT 5,
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE CASCADE
ON UPDATE CASCADE
);
5.2. Создание и удаление таблиц
Настало время познакомить вас с оставшимися таблицами базы данных «Авиапере- возки». Рекомендуем вам, прежде чем приступать к дальнейшей работе, освежить в памяти описание этой предметной области, приведенное в главе 1.
Поскольку в главе 3 вы уже создавали таблицы с помощью команды CREATE TABLE,
то мы изберем такую стратегию: мы представим вам команды для создания остав- шихся таблиц, но выполнять эти команды уже не требуется, достаточно будет только просматривать описания таблиц с помощью команды \d утилиты psql.
В главе 3 мы сначала представляли описание каждой создаваемой таблицы: назна- чения и имена столбцов, их типы данных и ограничения, которым каждый столбец должен удовлетворять. А уже после этого мы переходили непосредственно к SQL- команде создания таблицы в базе данных — CREATE TABLE. Но в этой главе мы, во избежание повторов, сразу будем показывать команды CREATE TABLE, дополняя их подробными комментариями.
Если вы еще не запустили утилиту psql, то запустите ее и подключитесь к базе данных demo с учетной записью пользователя СУБД с именем postgres:
psql -d demo -U postgres
Выберите в качестве текущей схемы схему bookings:
SET search_path TO bookings;
105
Глава 5. Основы языка определения данных
Если вы уже были подключены к другой базе данных, то можете, не выходя из ути- литы psql, просто подключиться к нужной вам базе данных с помощью команды
\
connect. Напомним, что команды, имена которых начинаются с символа «\», яв- ляются не SQL-командами, а командами утилиты psql. Конечно, за этими короткими командами, например \d, могут скрываться сложные SQL-запросы к системным таб- лицам базы данных. Просто утилита psql избавляет пользователя от необходимости вводить эти сложные запросы. Для подключения к базе данных demo изнутри psql сделайте так:
\connect demo
Существует и сокращенный вариант этой команды:
\c demo
При создании таблиц необходимо учитывать связи между ними. Поэтому сначала должны создаваться ссылочные таблицы, а потом — ссылающиеся. Конечно, возмож- на ситуация, когда образуется иерархия таблиц. Таблица, находящаяся в середине такой иерархии, выполняет обе роли: ссылающейся и ссылочной таблицы. Тогда нуж- но продвигаться «вниз» от вершины иерархии, где находится таблица, не имеющая внешних ключей.
Если в базе данных нет циклических ссылок таблиц друг на друга, то всегда существу- ет таблица (или таблицы), которая не ссылается ни на какие другие таблицы. С нее и нужно начинать создание базы данных. Перед созданием очередной таблицы, име- ющей внешние ключи, уже должны существовать все ссылочные таблицы для нее.
При наличии циклических ссылок таблиц друг на друга придется воспользоваться командой ALTER TABLE, о которой речь пойдет в следующем разделе этой главы.
Поскольку две первые таблицы — «Самолеты» (aircrafts) и «Места» (seats) — мы уже изучили в главе 3, то перейдем к таблице «Аэропорты» (airports). Она не име- ет внешних ключей.
В этой таблице в качестве кода аэропорта служат трехбуквенные обозначения, утвер- жденные специальной организацией. При этом используются только буквы латин- ского алфавита. Каждый аэропорт имеет также и полное название. Оно не всегда совпадает с названием города, в котором аэропорт находится. Для города не преду- смотрено отдельной сущности, поэтому таблицы городов нет. Однако название го- рода присутствует в виде атрибута city. Назначение остальных атрибутов ясно из комментариев, приведенных в SQL-команде.
Комментарии в языке SQL обозначаются двумя символами «дефис». При создании таблиц в среде утилиты psql вводить комментарии не нужно, но если вы создаете
106