Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
Глава 5 Основы языка определения данных Как мы уже говорили ранее, язык SQL традиционно разделяется на две группы команд. Первая из них предназначена для определения данных, т. е. для создания объектов базы данных, таких, например, как таблицы. Вторая группа команд служит для выполнения различных операций с данными, таких как вставка строк в таблицы, выполнение запросов к ним, обновление и удаление строк из таблиц. В этой главе мы сосредоточимся на командах первой группы, т. е. на определении данных. Рассмотрим все таблицы базы данных «Авиаперевозки». 5.1. Значения по умолчанию и ограничения целостности В последующих разделах этой главы в качестве «опорной» базы данных мы будем использовать базу данных «Авиаперевозки», описанную в первой главе. Однако ос- новные сведения о значениях по умолчанию и ограничениях мы проиллюстрируем на той простой базе данных, состоящей из двух таблиц — «Студенты» и «Успевае- мость», о которой речь шла также в первой главе пособия. Сначала представим описание таблицы «Студенты» (students). Она имеет следую- щую структуру (т. е. набор атрибутов и их типы данных): Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения № зачетной книжки record_book Числовой numeric(5) NOT NULL Ф. И. О. name Символьный text NOT NULL Серия документа doc_ser Числовой numeric(4) Номер документа doc_num Числовой numeric(6) Для атрибута «Серия документа, удостоверяющего личность» мы выбрали числовой тип, хотя, пожалуй, более дальновидным был бы выбор символьного типа (см. зада- ние 10 в конце главы). 95 Глава 5. Основы языка определения данных Теперь перейдем к таблице «Успеваемость» (progress). Ее структура такова: Описание атрибута Имя атрибута Тип данных Тип PostgreSQL Ограничения № зачетной книжки record_book Числовой numeric(5) NOT NULL Учебная дисциплина subject Символьный text NOT NULL Учебный год acad_year Символьный text NOT NULL Семестр term Числовой numeric(1) NOT NULL term = 1 OR term = 2 Оценка mark Числовой numeric(1) DEFAULT 5 mark >= 3 AND mark <= 5 В описаниях таблиц «Студенты» и «Успеваемость» есть колонки «Ограничения». Они уже содержат конкретные сведения, хотя ограничения мы еще детально не рассмат- ривали. Таким образом, мы немного забежали вперед, но по мере рассмотрения огра- ничений вам будет становиться понятно назначение каждого из них в обеих таб- лицах. Итак, начнем со значений по умолчанию. При работе с базами данных нередко возникают ситуации, когда то или иное значение является типичным для какого-то конкретного столбца. Например, если мы при проектировании таблицы «Успевае- мость» (progress), предназначенной для учета успеваемости студентов, знаем, что их успехи, как правило, заслуживают оценки «отлично», то в команде CREATE TABLE мы можем отразить этот факт с помощью ключевого слова DEFAULT: CREATE TABLE progress ( ... mark numeric( 1 ) DEFAULT 5, ... ); Теперь перейдем к рассмотрению различных видов ограничений (constraints). Будем придерживаться того же порядка, в котором они представлены в документации. Первым будет ограничение CHECK. Для его создания используется ключевое сло- во CHECK, за которым следует выражение в круглых скобках, содержащее одно или несколько условий, налагаемых на атрибуты таблицы. Это ограничение бывает двух видов: уровня атрибута и уровня таблицы. Различие между ними только в синтакси- ческом оформлении: в обоих случаях в выражении могут содержаться обращения не только к одному, но и к нескольким атрибутам таблицы. В первом случае ограниче- ние CHECK является частью определения одного конкретного атрибута, а во втором случае оно записывается как самостоятельный элемент определения таблицы. 96 5.1. Значения по умолчанию и ограничения целостности Каждое ограничение имеет имя. Мы можем задать его сами с помощью ключевого слова CONSTRAINT. Если же мы этого не сделаем, тогда СУБД сформирует имя автома- тически. Когда мы задаем имя сами, мы можем выбрать его с учетом сути налагаемых ограничений, с позиции предметной области. Если же это имя формирует СУБД, оно будет сформировано «механически», т. к. СУБД не знает ни сути этих ограничений, ни специфики предметной области. В качестве примера приведем ограничения, налагаемые на атрибуты term и mark из таблицы «Успеваемость». Семестр может иметь только два значения: 1 и 2. Отметка фактически может иметь только три значения: 3, 4 или 5. CREATE TABLE progress ( ... term numeric( 1 ) CHECK ( term = 1 OR term = 2 ), mark numeric( 1 ) CHECK ( mark >= 3 AND mark <= 5 ), ... ); В данном случае можно и не давать этим ограничениям какие-либо специфические имена, поскольку суть этих ограничений очевидна. Тем не менее, поскольку имена ограничений используются в тех сообщениях, которые выводит СУБД при попытке нарушения ограничений, все же можно придумать для них осмысленные имена, ко- торые облегчат понимание причин появления сообщений об ошибках. В качестве примера приведем ограничение на допустимые значения атрибута mark, а оформим его как ограничение уровня таблицы: CREATE TABLE progress ( ... mark numeric( 1 ), CONSTRAINT valid_mark CHECK ( mark >= 3 AND mark <= 5 ), ... ); Следующим видом ограничений, который мы рассмотрим, будет ограничение NOT NULL. Оно означает, что в столбце таблицы, на который наложено это ограничение, должны обязательно присутствовать какие-либо определенные значения. При разра- ботке баз данных, исходя из логики конкретной предметной области, зачастую требу- ется использовать это ограничение. Как сказано в документации, оно функциональ- но эквивалентно ограничению CHECK ( column_name IS NOT NULL) , но в PostgreSQL создание явного ограничения NOT NULL является более эффективным подходом. 97 Глава 5. Основы языка определения данных Еще один вид ограничений — это ограничение уникальности UNIQUE. Такое огра- ничение, наложенное на конкретный столбец, означает, что все значения, содержа- щиеся в этом столбце в различных строках таблицы, должны быть уникальными, т. е. не должны повторяться. Ограничение уникальности может включать в себя и несколько столбцов. В этом случае уникальной должна быть уже комбинация их зна- чений. Когда в ограничение уникальности включается только один столбец, то можно задать ограничение непосредственно в определении столбца. Например, для таблицы «Сту- денты» было бы логично потребовать, чтобы уникальными были значения столбца record_book: CREATE TABLE students ( record_book numeric( 5 ) UNIQUE, ... ); Это ограничение можно было бы записать и так, дав ему осмысленное имя: CREATE TABLE students ( record_book numeric( 5 ), ... CONSTRAINT unique_record_book UNIQUE ( record_book ), ... ); Опять обратимся к таблице «Студенты» и покажем, как можно создать ограничение уникальности, включающее более одного столбца. В этой таблице первичным клю- чом является столбец record_book, но очевидно, что комбинация значений серии и номера документа, удостоверяющего личность, является уникальной. Поэтому мож- но модифицировать определение таблицы таким образом: CREATE TABLE students ( ... doc_ser numeric( 4 ), doc_num numeric( 6 ), ... CONSTRAINT unique_passport UNIQUE ( doc_ser, doc_num ), ... ); При добавлении ограничения уникальности автоматически создается индекс на ос- нове B-дерева для поддержки этого ограничения. 98 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 используются эти же варианты подходов по отношению к обеспечению ссылочной целостности. Аналогом каскадного уда- ления является каскадное обновление: |