Языковые средства описания данных

Язык определения данных для реляционной модели данных включает следующие возможности:

• создание домена,

• создание отношения,

• определение ограничений целостности.

В реально существующих реляционных СУБД (РСУБД), работающих на основе реляционной модели данных, в качестве ЯОД используется некоторое подмножество языка SQL, предоставляющее соответствующие средства. Так, в стандарте SQL-92 определено предложение CREATE DOMAIN для создания доменов, однако далеко не все СУБД такую возможность поддерживают. Но во всех РСУБД есть предложение CREATE TABLE, позволяющее создать таблицу (отношение реляционной модели данных) и определить для нее необходимые ограничения целостности.

Соответствие между структурными компонентами реляционной модели данных и элементами реляционной базы данных, определяемыми с помощью SQL, приведено в таблице 4.1.

Таблица 4.1. Соответствие между компонентами РМД и элементами базы данных

Структурный компонент реляционной модели данных	Элемент реляционной базы данных
Отношение	Таблица
Домен	Тип данных
Атрибут	Колонка таблицы
Кортеж отношения	Строка таблицы

Важным компонентом при описании структуры базы данных является описание ограничений, накладываемых на данные. Описание ограничений целостности, поддерживаемых моделью данных, осуществляется также средствами языка описания данных. Типы ограничений целостности, определяемые средствами SQL, приведены в таблице 4.2.

Таблица 4.2. Определение ограничений целостности в SQL

Тип ограничения целостности	Представление средствами SQL
Уникальность значений	PRIMARY KEY или UNIQUE
Обязательность значений	NULL или NOT NULL
Допустимость значений	CHECK
Ссылочное ограничение	FOREIGN KEY

Подмножество языка SQL для описания данных включает в свой состав три основных предложения:

CREATE тип_объекта – создать соответствующий объект базы данных,

ALTER тип_объекта – изменить соответствующий объект базы данных,

DROP тип_объекта – удалить указанный объект базы данных.

В данном разделе в качестве объекта базы данных рассматривается таблица; в соответствии с этим в качестве типа_объекта используется ключевое слово TABLE.

Рассмотрим основные возможности SQL DB2® UDB, используемые для описания данных.

В описании использованы следующие обозначения:

1. Прописными буквами полужирным шрифтом записываются те фрагменты, которые при кодировании предложения SQL должны копироваться именно так, как указано в описании, например, CREATE TABLE.

2. Строчными буквами и курсивом выделены те понятия, которые при кодировании предложения SQL должны заменяться собственными значениями; например, запись имя_таблицы означает, что вместо нее должно быть указано конкретное имя таблицы.

3. Специальные символы, встречающиеся в описании предложения и выделенные полужирным шрифтом, должны кодироваться так, как они записаны.

4. Не обязательная часть предложения заключается в квадратные скобки; при кодировании предложения она может быть опущена.

5. Многоточие означает, что предшествующая ему часть предложения может быть повторена произвольное число раз.

В записи исходных текстов часто возникает необходимость добавлять комментарии, поясняющие особенности использования соответствующих конструкций языка. Комментарии в языке SQL начинаются двумя знаками «минус» и заканчиваются концом строки, например:

-- это комментарий

В записи текста комментария могут быть использованы любые символы.

Способы именования объектов

Для именования объектов базы данных (в данном случае, таблиц и колонок в таблицах) используются идентификаторы.

Идентификаторы языка делятся на обычные идентификаторы и идентификаторы с ограничителями. Обычный идентификатор – это последовательность букв, цифр и символов подчеркивания (_), начинающаяся с буквы. Обычный идентификатор не должен совпадать с ключевыми словами SQL.

Примеры: A12 Tab_Name DeptID

Язык SQL считается языком, не чувствительным к регистру, т.е. в записи обычных идентификаторов и ключевых слов SQL можно использовать и прописные, и строчные буквы английского алфавита, например, идентификаторы DeptID и DEPTID определяют одно и то же имя; слова SELECT и select определяют одно и то же ключевое слово.

Идентификаторы с ограничителями – любой текст, заключенный в парные двойные кавычки, например: ”WKLY-SAL” ”Region 5”. Идентификаторы с ограничителями чувствительны к регистру; так, ”WKLY SAL” и ”wkly sal” определяют разные идентификаторы. Идентификаторы с ограничителями целесообразно использовать, в частности, в тех случаях, когда по каким-либо причинам необходимо использовать имя, совпадающее с зарезервированным словом, например, ”UNION”, или многословное имя, отражающее семантику объекта, например, ”Department Name”.

Основные типы данных

DB2® поддерживает определенный набор встроенных типов данных. Для всех типов данных допускается значение NULL, которое, по своей сути, означает отсутствие значения.

В общем случае, базовые встроенные типы данных можно разделить на три группы: числовые типы данных, строковые типы данных и типы данных, используемые для представления даты и времени.

Числовые типы данных используются для представления целых, вещественных и десятичных чисел. Все числа имеют знак и точность. Число нуль (0) рассматривается как положительное. Точность определяется как количество битов или десятичных цифр, используемых в представлении числа, за исключением знака числа.

Для представления целых чисел используются следующие типы данных:

SMALLINT – короткое целое; двух байтное целое, точность представления 5 десятичных цифр (или 15 битов), диапазон представления – от -32 768 до 32 767 (от -2¹⁵ до 2¹⁵ – 1).

INTEGER (или INT) – целое; четырех байтное целое, точность представления 10 десятичных цифр (или 31 бит), диапазон – от -2 147 483 648 до 2 147 483 647 (от -2³¹ до 2³¹ – 1).

BIGINT – длинное целое; восьми байтное целое, точность представления 19 десятичных цифр (или 63 бита), диапазон представления – от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807

(от -2⁶³ до 2⁶³ – 1).

Для представления вещественных чисел, или чисел с плавающей запятой, используются следующие типы данных:

REAL – вещественные числа обычной точности; для представления числа используется 31 бит. Число со знаком; абсолютная величина числа может быть представлена как 0 или в диапазоне от

1,175*10^-37 до 3,402*10⁺³⁸.

DOUBLE [ PRECISION ] – вещественные числа двойной точности; для представления числа используется 63 бита. Число со знаком; абсолютная величина числа может быть представлена как 0 или в диапазоне от 2,225*10^-307 до 1,79769*10⁺³⁰⁸.

FLOAT( n ) – вещественное число обычной или двойной точности, в зависимости от того, как задано значение n. В общем случае, 1 ≤ n ≤ 53. Значения 1 ≤ n ≤ 24 соответствуют вещественному числу обычной точности (независимо от значения n для представления числа используется 31 бит), 25 ≤ n ≤ 53 – двойной точности (независимо от значения n для представления числа используется 63 бита). Если значение n не указано, определяется вещественное число двойной точности.

Для представления десятичных чисел используются следующие типы данных:

DECIMAL( p,s ) (или DEC( p,s )) – десятичное число; представляется в виде упакованного десятичного числа (в каждом байте размещаются по две десятичные цифры) с неявно заданным положением десятичной запятой, определяемым значениями точности p и масштаба s числа. Точность p задает общее количество цифр в записи числа, p ≤ 31. Масштаб s задает количество десятичных цифр в дробной части числа и не может быть отрицательным или превышать точность числа: 0 ≤ s ≤ p. Если значение s опущено, оно принимается равным 0. Если опущено значение p, оно принимается равным 5. Таким образом, указание

DECIMAL эквивалентно заданию DECIMAL(5,0). Диапазон представления чисел – от – n до + n, где значение n зависит от заданных значений точности и масштаба; максимальный диапазон – от -10³¹ + 1 до 10³¹ – 1.

NUMERIC( p,s ) (или NUM( p,s )) – полностью эквивалентно DECIMAL.

Строковые типы данных используются для представления символьных строк. Символьная строка представляет собой последовательность байтов. Длина строки представляет собой количество байтов в последовательности. Если длина строки равна нулю, значение такой строки определяется как пустая строка. Пустая строка не эквивалентна строке, имеющей NULL значение.

Для представления символьных строк используются следующие типы данных:

CHARACTER [ ( len ) ] (или CHAR [ ( len ) ]) – символьные строки фиксированной длины, заданной параметром len. Значение len лежит в диапазоне 1 ≤ len ≤ 254. Если значение len не указано, оно принимается равным 1. Все значения данного типа имеют одинаковую длину len. Если в записи символьной строки данного типа использовано меньшее, чем len, количество символов, строка дополняется пробелами справа до len символов. Если в записи строки использовано большее, чем len, количество символов, строка усекается справа до len символов.

VARCHAR [ ( len ) ] – символьные строки переменной длины. Значение len задает максимально возможную длину строки и лежит в диапазоне 1 ≤ len ≤ 32672. Если в записи строки использовано количество символов rlen ≤ len, длина такой строки будет равна rlen. Если в записи строки использовано большее, чем len, количество символов, строка усекается справа до len символов, и длина такой строки будет равна len. Если значение len не указано, оно принимается равным 1.

Типы данных дата – время используются для представления даты и времени. Хотя значения даты и времени можно использовать с некоторыми арифметическими и строковыми операциями, они не являются ни числами, ни строками.

Для представления даты и времени используются следующие типы данных:

DATE – представление даты; значение, представленное в виде трех частей: год, месяц и день. Год представляется в диапазоне от 0001 до 9999, месяц – в диапазоне от 1 до 12, день – в диапазоне от 1 до х, где значение х зависит от месяца. Внутреннее представление даты – последовательность из 4-х байтов, каждый байт содержит две упакованные десятичные цифры. Первые два байта представляют год, третий байт – месяц и последний байт – день.

TIME – представление времени; значение, представленное в виде трех частей: часы, минуты и секунды. Используется 24-х часовое представление времени. Часы представляются в диапазоне от 0 до 24, минуты и секунды – в диапазоне от 0 до 59. Если значение часа установлено как 24, минуты и секунды устанавливаются в 0. Внутреннее представление времени – последовательность из 3-х байтов, каждый байт содержит две упакованные десятичные цифры. Первый байт представляет часы, второй – минуты и последний – секунды.

TIMESTAMP – представление временной метки; значение, представляющее совместно дату и время. Дата и время представлены так, как описано выше, за исключением того, что к значению времени добавлены микросекунды. Внутреннее представление – последовательность из 10 байтов, каждый байт содержит две упакованные десятичные цифры. Первые 4 байта представляют дату, следующие 3 байта – время и последние 3 байта – микросекунды.

Внутреннее представление даты и времени прозрачно для пользователя; пользователь может представлять дату и время в виде строк. Это тем более важно, что в языке нет констант типа DATE, TIME и

TIMESTAMP. Строка, представляющая дату и время, может содержать только цифры и некоторые специальные знаки.

Формат представления даты зависит от территории. Допустимые форматы представления даты приведены в таблице 4.3.

Таблица 4.3. Форматы представления даты

Название формата	Аббревиатура	Формат представления даты	Пример	Формат представления времени	Пример
Международная Организация стандартов (International Standards Organization)	ISO	yyyy-mm-dd	1991-10-27	hh.mm.ss	13.30.05
Стандарт IBM США	USA	mm/dd/yyyy	10/27/1991	hh:mm AM или PM	1:30 PM
Европейский стандарт IBM	EUR	dd.mm.yyyy	27.10.1991	hh.mm.ss	13.30.05

В записи времени в стандартах ISO и EUR значение секунд (. ss), а в стандарте USA значение минут (: mm) могут быть опущены; в этом случае опущенные значения принимаются равными нулю.

Строка, представляющая значение типа TIMESTAMP, имеет вид: yyyy - mm - dd - hh. mm. ss. nnnnnn. Лидирующие нули в записи месяца, дня, часов могут быть опущены. В записи микросекунд могут быть опущены все цифры или лидирующие нули. В качестве значений всех опущенных цифр подставляется нуль. Так, запись 1991-3-2-8.30.00 эквивалентна 1991-03-02-08.30.00.000000.

Выражения

При определении структуры базы данных (создании таблиц) необходимо указывать и некоторые ограничения, накладываемые на возможные значения столбцов таблицы. При записи ограничений используются выражения. Рассмотрим, прежде всего, правила записи и вычисления тех выражений, которые могут быть использованы при создании таблиц.

В общем случае выражение представляет собой совокупность операндов, разделенных знаками операций. В качестве операнда можно использовать константу, имя колонки или выражение, заключенное в круглые скобки. Операнду может предшествовать знак + или –.

В качестве операции может быть задана одна их следующих операций: CONCAT, /, *, +, -. В качестве операции CONCAT допускается использовать ||.

Если хотя бы один из операндов в выражении имеет значение NULL, результатом вычисления выражения является NULL; в противном случае результат вычисления выражения определяется типом и значениями операндов.

Константы

Константа определяет значение. Константы делятся на строковые и числовые.

Строковая константа определяет строку переменной длины и представляется последовательностью алфавитно-цифровых символов, заключенную в парные одиночные кавычки (апострофы), например:

' string for example '. Значением строковой константы является сама строка, расположенная между апострофами.

Длина строковой константы определяется количеством символов в записи константы. Так, в приведенном примере длина константы равна 18. Длина строковой константы не должна превышать 32672.

Если в записи константы необходимо использовать сам символ апострофа, он должен быть удвоен. Так, запись 'DON''T CHANGE' определяет строку длиной 12 символов, значением которой является строка DON'T CHANGE.

Строковые константы чувствительны к регистру, используемому при записи букв. Так, строковые константы 'string for example' и 'String for Example' имеют разное значение.

Числовые константы, в свою очередь, делятся на целые, вещественные и десятичные. Все константы обладают свойством not null. Значение числовой константы -0 эквивалентно значению 0.

Целая константа определяет целое значение со знаком или без знака, записанное с помощью максимум 19 цифр. Целая константа не содержит десятичной точки. Целая константа имеет тип integer, если ее значение лежит в диапазоне 4-х байтного значения, и тип bigint, если ее значение выходит за диапазон 4-х байтного значения. Константа, значение которой выходит за диапазон представления, определяемого типом bigint, рассматривается как десятичная константа.

Примеры: 64 -15 +100 32767 720176 12345678901

Вещественная константа (или константа с плавающей запятой) определяет вещественное число, в записи которого используются два числа, разделенных буквой E. В записи первого числа могут быть указаны знак и десятичная точка. Второе число представляется в виде целого числа, которому может предшествовать знак. Тип данных вещественной константы – вещественное число двойной точности. Значением константы является произведение первого числа на 10 в степени, равной второму числу в записи константы. Количество символов в записи константы не должно превышать 30.

Примеры: 15E1 (значение константы 15*10¹ = 150) 2.E5 2.2E-1 +5E+2

Десятичная константа представляется числом со знаком или без знака, которое либо содержит десятичную точку, либо значение константы выходит за диапазон представления bigint. В записи десятичной константы должно быть не более 31 цифры. Точность константы определяется общим количеством цифр в ее записи, включая лидирующие и завершающие нули. Масштаб числа определяется количеством цифр, записанных справа от десятичной точки, включая завершающие нули.

Примеры: 25.5 (точность константы 3, масштаб 1) +37589.3333333333 1000. -15.

Если в записи двух последних констант точка не будет указана, тогда эти константы будут иметь тип integer.

Как уже говорилось, в языке нет специальных констант, представляющих дату и/или время; для этих целей используются обычные текстовые строки, содержащие значение даты и/или времени в соответствии с одним из указанных выше форматов. Однако часто возникает необходимость использовать текущие дату и/или время. Для этих целей используются особые конструкции языка – специальные регистры. Специальный регистр – это область хранения, определяемая для прикладной программы менеджером базы данных. Он используется для хранения информации, на которую можно ссылаться из предложений SQL. Ссылка на специальный регистр – это ссылка на значение, предоставляемое текущим сервером.

Для представления даты и времени используются следующие специальные регистры:

CURRENT DATE – специальный регистр, определяющий дату;

CURRENT TIME – специальный регистр, определяющий время;

CURRENT TIMESTAMP – специальный регистр, определяющий временн у ю метку.

Специальные регистры для представления даты и времени определяют свое значение на основе чтения системных часов во время исполнения предложения SQL. Если в одном предложении SQL какой-либо специальный регистр даты – времени используется несколько раз или вместе с другими специальными регистрами даты – времени, все значения базируются на однократном чтении часов.

Операция конкатенации

Операцию конкатенации (CONCAT или ||) можно использовать с операндами строкового типа. Операция выполняет сцепление двух строковых операндов и образует строковое выражение. Операндами операции конкатенации должны быть совместимые строки. Результат определяется как строка, состоящая из значения первого операнда, за которым непосредственно следует значение второго операнда. Тип результата определяется как CHAR, если оба операнда имеют тип CHAR и длина полученной строки не превышает 254 символов; в противном случае тип результата определяется как VARCHAR.

Арифметические операции

Арифметические операции можно использовать с операндами числовых типов. Префиксная операция + (унарный плюс) не изменяет значение своего операнда. Префиксная операция – (унарный минус) изменяет знак не нулевого операнда. Бинарные (инфиксные) операции (/, *, +, -) определяют сложение, вычитание, умножение и деление, соответственно. Значение второго операнда в операции деления не должно быть равно нулю.

Имеются некоторые особенности использования арифметических операций с операндами целых, вещественных и десятичных типов.

Если оба операнда, участвующие в бинарной операции, относятся к целому типу, тип операнда определяется как INT, если только один из операндов (или оба) не имеют тип BIGINT; в этом случае результат также будет иметь тип BIGINT. Операция деления выполняется как целочисленная операция (дробная часть результата отбрасывается).

Если хотя бы один из операндов относится к вещественному типу, оба операнда преобразуются к типу двойной точности, и результат имеет тип вещественного числа двойной точности.

Если один из операндов относится к десятичному типу, а другой – к целому, целочисленный операнд преобразуется к десятичному типу в соответствии с правилами, приведенными в таблице 4.4, и операция выполняется над десятичными операндами.

Таблица 4.4. Правила преобразования целых типов в десятичный

Целочисленный тип данных	Десятичный тип данных
SMALLINT	DECIMAL(5,0)
INT	DECIMAL(11,0)
BIGINT	DECIMAL(19,0)

Если оба операнда, участвующие в операции, относятся к десятичному типу, результат также имеет десятичный тип; точность и масштаб результата зависят от точности и масштаба операндов и от используемой операции.

Пусть в операции участвуют операнды типа DECIMAL(p1, s1) и DECIMAL(p2, s2). Тогда результат операции будет иметь тип DECIMAL(p, s), где значения p и s вычисляются по правилам, приведенным в таблице 4.5.

Таблица 4.5. Точность и масштаб результата арифметических операций

Операция	Точность p	Масштаб s
Сложение, вычитание	max(p1 – s1, p2 – s2) + max(s1, s2) + 1, но не больше 31	max(s1, s2)
Умножение	p1 + p2, но не больше 31	s1 + s2, но не больше p
Деление		31 – p1 + s1 – s2, но не может быть отрицательным

Операции с данными типа дата-время

Значения даты и времени можно увеличивать, уменьшать и вычитать. Эти операции могут включать десятичные числа, получившие названия продолжительности (durations). Продолжительность – это число, представляющее интервал времени. Определены четыре типа продолжительности: помеченная продолжительность, продолжительность даты, продолжительность времени и продолжительность временной метки.

Помеченная продолжительность задает определенное количество времени, которое представляется некоторым значением, за которым следует метка, определяющая единицу измерения времени. Метка, определяющая единицу измерения времени, задается с помощью следующих зарезервированных слов: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND (допускается использование ключевых слов во множественном числе – YEARS, MONTHS и т.д.), например: 2 MONTHS + 14 DAYS (2 месяца и 14 дней).

Продолжительность даты представляет количество лет, месяцев и дней, выраженное целым десятичным числом в виде yyyymmdd, где yyyy представляет количество лет, mm – количество месяцев и dd – количество дней, например: 214 (0 лет, 2 месяца и 14 дней).

Продолжительность времени представляет количество часов, минут и секунд, выраженное целым десятичным числом в виде hhmmss, где hh представляет количество часов, mm – количество минут и ss – количество секунд, например: 12530 (1 час 25 минут 30 секунд).

Продолжительность временной метки представляет количество лет, месяцев, дней, часов, минут, секунд и микросекунд, выраженное десятичным числом с дробной частью в виде yyyymmddhhmmss.zzzzzz, где yyyy, mm, dd, hh, mm, ss, zzzzzz представляют количество лет, месяцев, дней, часов, минут, секунд и (в дробной части) микросекунд, соответственно.

Значения типа дата – время могут быть использованы только с операциями сложения и вычитания. В качестве одного из операндов обязательно указывается значение типа даты и/или времени. Представление второго операнда зависит от операции.

Если используется операция сложения, тогда второй операнд должен обязательно определять продолжительность в соответствии со следующими правилами:

• если один из операндов – дата, второй операнд должен быть обязательно продолжительностью даты или помеченной продолжительностью с метками YEAR[S], MONTH[S], DAY[S];

• если один из операндов – время, второй операнд должен быть обязательно продолжительностью времени или помеченной продолжительностью с метками HOUR[S], MINUTE[S], SECOND[S];

• если один из операндов – временная метка, второй операнд должен быть продолжительностью. В этом случае допускается продолжительность любого типа.

Результатом вычисления выражения является значение даты и/или времени, в соответствии с типом первого операнда.

Если используется операция вычитания, в качестве первого операнда должно обязательно указываться значение типа даты и/или времени; второй операнд может определять и продолжительность даты и/или времени, и значение даты и/или времени, в соответствии со следующими правилами:

• если первый операнд – дата, второй операнд должен быть датой, продолжительностью даты, строковым представлением даты или помеченной продолжительностью с метками YEAR[S], MONTH[S], DAY[S];

• если второй операнд – дата, первый операнд должен быть датой или строковым представлением даты;

• если первый операнд – время, второй операнд должен быть временем, продолжительностью времени, строковым представлением времени или помеченной продолжительностью с метками HOUR[S],

•MINUTE[S], SECOND[S];

• если второй операнд – время, первый операнд должен быть временем или строковым представлением времени;

• если первый операнд – временная метка, второй операнд должен быть временной меткой, строковым представлением временной метки или продолжительностью любого типа;

• если второй операнд – временная метка, первый операнд должен быть временной меткой или строковым представлением временной метки.

Результатом вычитания продолжительности из значения даты и/или времени является значение даты и/или времени, в соответствии с типом первого операнда. Результатом вычитания одного значения даты и/или времени из другого является продолжительность, тип которой определяется типом операндов. Результаты выполнения арифметических операций с датами учитывают особенности календарного представления даты. Например, если Date1 имеет значение 31 января 2001 года (последний день месяца), и вычисляется выражение Date1 + 31 DAYS, в результате будет получена дата 3 марта 2001 года (учитывая, что в феврале 2001 года 28 дней). Если вычисляется выражение Date1 + 1 MONTH, в результате будет получена дата 28 февраля 2001 года (последний день следующего месяца). Если же Date1 имеет значение 31 января 2004 года, тогда в результате вычисления выражения Date1 + 31 DAYS будет получена дата 2 марта 2004 года (в феврале 29 дней), а в результате вычисления выражения Date1 + 1 MONTH – дата 29 февраля 2004 года.

Создание таблицы

Рассмотрим основной синтаксис предложения CREATE TABLE для создания базовой таблицы базы данных в пределах тех возможностей, которые определяются реляционной моделью данных (т.е. отображение схемы отношения и указание ссылочных ограничений целостности).

Предложение CREATE TABLE для создания базовой таблицы имеет следующий синтаксис:

CREATE TABLE имя_таблицы (

имя_колонки тип_данных [ ограничение_обязательности ] [ ограничение_целостности_на_колонку ] [ спецификация_генерируемого_значения ]

[, …]

[, ограничение_целостности_на_таблицу ]

[, … ]

)

Здесь

имя_таблицы – идентификатор, задает имя таблицы;

имя_колонки – идентификатор, уникальный в пределах данной таблицы; задает имя соответствующей колонки;

тип_данных – тип данных для колонки;

ограничение_обязательности – задает ограничение обязательности значения данной колонки;

ограничение_целостности_на_колонку – ограничение целостности, накладываемое на данную колонку;

ограничение_целостности_на_таблицу – ограничение целостности, накладываемое на одну или несколько колонок создаваемой таблицы.

генерируемое_значение – указывает, какое значение должно быть вставлено в данную колонку в строке таблицы, если в операции вставки значение данной колонки не указано.

Если ограничения целостности накладываются на одну колонку таблицы, они могут быть заданы и как ограничение_целостности_на_колонку, и как ограничение_целостности_на_таблицу. Если же некоторое ограничение целостности затрагивает несколько колонок таблицы, например, составной первичный ключ, такое ограничение может быть задано только как ограничение_целостности_на_таблицу.

Ограничения целостности на колонку и на таблицу задаются следующим образом:

[ CONSTRAINT имя_ограничения ] ограничение

имя_ограничения – задает имя ограничения. Должно быть уникальным в пределах данной таблицы. Если имя ограничения не указывается, система сама генерирует 18-символьный уникальный для таблицы идентификатор;

ограничение – задает конкретное ограничение целостности. Конкретный способ записи ограничения зависит от того, какое ограничение – на колонку или на таблицу – используется.

Ограничение обязательности и ограничения целостности на колонку (одно или несколько) могут указываться в произвольном порядке.

Рассмотрим правила задания ограничений.

Ограничение обязательности

При задании ограничения обязательности допускается одно из двух значений – NOT NULL или NULL.

NOT NULL – признак обязательности значения данной колонки, означающий, что значение данной колонки не может быть опущено.

NULL – признак не обязательности значения данной колонки; указывает, что значение данной колонки может отсутствовать.

Если ограничение обязательности не указано, принимается значение NULL.

Ограничение уникальности

Ограничение уникальности на колонку имеет вид: UNIQUE или PRIMARY KEY. Ограничение уникальности на таблицу имеет вид: UNIQUE( имя_колонки, … ) или PRIMARY KEY( имя_колонки, … )

UNIQUE – определяет альтернативный ключ,

PRIMARY KEY – определяет первичный ключ.

Колонки, для которых определено ограничение уникальности (UNIQUE или PRIMARY KEY), должны быть объявлены как NOT NULL. Одна и та же последовательность колонок может быть указана только в одном ограничении уникальности (UNIQUE или PRIMARY KEY). В определении таблицы может быть указано несколько ограничений UNIQUE и только одно ограничение PRIMARY KEY.

Ограничение допустимости значения

Ограничение, определяющее допустимость значения, имеет вид:

CHECK( условие )

При этом условие, записанное в ограничении на колонку, может содержать имя только данной колонки; условие, записанное в ограничении на таблицу, может содержать любые (одно или более) имена колонок из данной таблицы.

В записи условия используются предикаты и логические операции – унарная операция НЕ (NOT) и бинарные операции И (AND) и ИЛИ (OR). В общем случае условие может иметь следующий вид:

[ NOT ] предикат [ бинарная_операция [ NOT ] предикат ] …

Предикат представляет собой некоторое условное выражение, результатом которого может быть одно из трех значений – истина (true), ложь (false) и не определенное (NULL). В соответствии с этим логические операции реализуют троичную логику. Правила вычисления логических операций приведены в таблице 4.6.

Таблица 4.6. Правила вычисления логических операций

P	Q	NOT P	P AND Q	P OR Q
true	true	false	true	true
true	false	false	false	true
true	NULL	false	NULL	true
false	true	true	false	true
false	false	true	false	false
false	NULL	true	false	NULL
NULL	true	NULL	NULL	true
NULL	false	NULL	false	NULL
NULL	NULL	NULL	NULL	NULL

В записи предиката используются выражения, правила записи и вычисления которых были рассмотрены ранее.

В качестве предиката могут быть использованы основной предикат и предикаты BETWEEN, IN и LIKE. Рассмотрим каждый из этих предикатов более подробно.

Основной предикат имеет следующий вид:

выражение операция_отношения выражение

В качестве операции_отношения могут быть использованы операции = (равно), <> (не равно), < (меньше), > (больше), <= (меньше или равно), >= (больше или равно).

Основной предикат позволяет сравнить два значения. Если хотя бы одно из двух сравниваемых значений не определено (NULL), результатом будет NULL. В противном случае результатом вычисления будет значение «истина» или «ложь».

Предикат BETWEEN имеет следующий вид:

выражение1 [ NOT ] BETWEEN выражение2 AND выражение3

Значения всех выражений, используемых в предикате, должны быть сопоставимыми и допускать сравнение; кроме того, должно выполняться соотношение: выражение2 ≤ выражение3

Данный предикат позволяет проверить условие выражение2 ≤ выражение1 ≤ выражение3.

Предикат выражение1 BETWEEN выражение2 AND выражение3 эквивалентен условию поиска

выражение1 >= выражение2 AND выражение1 <= выражение3.

Предикат выражение1 NOT BETWEEN выражение2 AND выражение3 эквивалентен условию поиска

NOT (выражение1 >= выражение2 AND выражение1 <= выражение3), или

выражение1 < выражение2 OR выражение1 > выражение3.

Результатом вычисления предиката BETWEEN является значение «истина», если данное условие выполняется, «ложь», если не выполняется, и не определено, если один из операндов имеет значение NULL. Результатом вычисления предиката NOT BETWEEN является значение «истина», если данное условие не выполняется, «ложь», если выполняется, и не определено, если один из операндов имеет значение NULL.

Предикат IN имеет следующий вид:

выражение [ NOT ] IN ( список_выражений )

список_выражений представляет собой перечисление выражений через запятую в произвольном порядке. Значение выражения должно быть сопоставимым со значениями выражений из списка.

Результатом вычисления предиката IN является значение:

• «истина», если значение выражения совпадает хотя бы с одним значением из списка;

• «ложь», если значение выражения не совпадает ни с одним значением из списка, и в списке нет неопределенных значений;

• не определено, если значение выражения не совпадает ни с одним значением из списка, и в списке есть неопределенные значения.

Результатом вычисления предиката NOT IN является значение:

• «истина», если значение выражения не совпадает ни с одним значением из списка, и в списке нет неопределенных значений;

• «ложь», если значение выражения совпадает хотя бы с одним значением из списка;

• не определено, если значение выражения не совпадает ни с одним значением из списка, и в списке есть неопределенные значения.

Предикат LIKE имеет следующий вид:

выражение [ NOT ] LIKE шаблон [ ESCAPE escape-символ ]

Предикат LIKE проверяет, соответствует ли строка, являющаяся результатом вычисления выражения, заданному шаблону.

Результатом вычисления предиката LIKE является значение «истина», если значение выражения удовлетворяет шаблону, «ложь», если не удовлетворяет, и не определено, если хотя бы один из аргументов имеет значение NULL. Результатом вычисления предиката NOT LIKE является значение «истина», если значение выражения не удовлетворяет шаблону, «ложь», если удовлетворяет, и не определено, если хотя бы один из аргументов имеет значение NULL.

Шаблон представляется в виде строки, в которой могут быть использованы следующие специальные мета символы:

_ (символ подчеркивания) – соответствует одному (любому) символу,

% – соответствует любой (возможно, пустой) цепочке символов.

Любой другой символ соответствует самому себе.

Если в записи шаблона необходимо использовать обычные символы _ и/или %, без их специального смысла, таким символам должен предшествовать escape-символ. Если он не задан, по умолчанию используется символ \. Так, шаблон 'A%' соответствует любой строке символов, начинающейся буквой А; шаблон 'A\%' соответствует строке, состоящей в точности из двух символов – А и %. Если нужно отменить специальный смысл самого символа \, ему также должен предшествовать escape-символ \. Так, шаблон 'A\\%' соответствует любой строке символов, начинающейся последовательностью из двух символов – А и \; шаблон 'A\\\%' соответствует строке символов, состоящей в точности из трех символов – A, \ и %.

Ссылочное ограничение

Ссылочное ограничение на колонку задается следующим образом:

REFERENCES имя­_родительской_таблицы [(имя_колонки_PK, …)] [ ON DELETE правило_удаления ] [ ON UPDATE правило_обновления ]

Ссылочное ограничение на таблицу задается следующим образом:

FOREIGN KEY ( имя_колонки_FK, … ) REFERENCES имя­_родительской_таблицы [(имя_колонки_PK, …)] [ ON DELETE правило_удаления ] [ ON UPDATE правило_обновления ]

Здесь

имя_колонки_FK – имена колонок внешнего ключа в создаваемой (дочерней) таблице. Каждое имя колонки в списке должно соответствовать имени колонки, определенной в дочерней таблице. В списке не должно одно и то же имя колонки указываться дважды. Колонки, перечисленные в ограничении, должны по порядку записи, количеству и типам данных совпадать с колонками первичного ключа родительской таблицы. Это означает, что в определении внешнего ключа должно быть указано столько же колонок, сколько колонок указано в первичном ключе родительской таблицы, и тип данных n -ой колонки в списке колонок внешнего ключа должен совпадать с типом данных n -ой колонки в списке колонок первичного ключа родительской таблицы;

имя­_родительской­­_таблицы – имя родительской таблицы, на которую ссылается создаваемая дочерняя таблица;

имя_колонки_PK – имена колонок первичного ключа в родительской таблице. Список имен колонок первичного ключа должен соответствовать множеству имен колонок (в любом порядке) первичного ключа или уникального ключа, определенных в родительской таблице. Если список имен колонок не указан, предполагается, что задан первичный ключ родительской таблицы;

правило_удаления – определяет, какие действия должны быть выполнены при удалении записи из родительской таблицы. Допускаются следующие значения:

RESTRICT или NO ACTION – удаление записи из родительской таблицы не происходит, если в дочерней таблице есть хотя бы одна запись, ссылающаяся на удаляемую;

CASCADE – вместе с удаляемой записью из родительской таблицы удаляются все ссылающиеся на нее записи из дочерней таблицы;

SET NULL – при удалении записи из родительской таблицы в колонки внешнего ключа ссылающихся на нее записей дочерней таблицы записывается значение NULL (колонки внешнего ключа должны допускать NULL значение);

правило_обновления – определяет, какие действия должны быть выполнены при модификации первичного ключа в родительской таблице. Допускаются только значения RESTRICT или NO ACTION, имеющие такой же смысл, как и в правиле удаления.

Если правило удаления (и/или обновления) не указано, по умолчанию принимается NO ACTION.

Указание RESTRICT или NO ACTION в качестве правила удаления или обновление определяет, когда проверяется соответствующее ограничение. Если указано RESTRICT, соответствующее ему ссылочное ограничение проверяется первым, перед проверкой всех других ограничений, в том числе и модифицирующих записи ссылочных ограничений CASCADE и SET NULL; если указано NO ACTION, тогда соответствующее ему ссылочное ограничение проверяется после проверки всех других ссылочных ограничений.

Спецификация генерируемого значения

При определении колонки в таблице можно для колонки указать спецификацию генерируемого значения. Данная спецификация позволяет указать, какое значение должно быть вставлено в строку таблицы, если в операции вставки значение данной колонки не указано.

Спецификация генерируемого значения может быть задана одним из трех способов: значение по умолчанию, автоинкрементное значение и генерируемое значение.

Рассмотрим эти способы.

Значение по умолчанию задается следующим образом:

[ WITH ] DEFAULT значение

В качестве значения обычно задается некоторая константа, тип которой должен соответствовать типу данных колонки.

Автоинкрементное значение задается следующим образом:

GENERATED ALWAYS AS IDENTITY [ опции ]

Опции позволяют установить такие характеристики автоинкрементного типа, как начальное значение (задается с помощью фразы START WITH) и шаг приращения (INCREMENT BY), минимальное и максимальное значение, возможность повторного вычисления значений и другие. Подробное описание опций автоинкрементного значения приведено в разделе «Дополнительные возможности». Если никакие опции не указаны, опции устанавливаются таким образом, чтобы генерировался натуральный ряд чисел: 1, 2, 3, …

Автоинкрементное значение может быть указано только для одной колонки таблицы и только для колонки целочисленного типа.

Генерируемое значение задается следующим способом:

GENERATED ALWAYS AS (выражение)

Выражение записывается в соответствии с правилами записи выражений, приведенными выше.

Примеры создания таблиц

Рассмотрим примеры создания таблиц.

Пример 1. Отношение ДЕТАЛЬ может быть определено следующим образом:

CREATE TABLE P(

P_ID SMALLINT NOT NULL CONSTRAINT P_PK PRIMARY KEY, --1

PNAME VARCHAR (20) NOT NULL CONSTRAINT P_UQ_01 UNIQUE, --2

PRICE DECIMAL (6,0) NOT NULL CONSTRAINT P_CH_01 CHECK (PRICE > 0) --3

)

Пояснения:

1 – определены ограничения первичного ключа (ограничение PRIMARY KEY, имя ограничения – P_PK) и обязательности значения;

2 – определены ограничения уникальности (имя ограничения – P_UQ_01) и обязательности значения;

3 – определено условие (имя ограничения – P_CH_01): значение атрибута должно быть строго положительным.

При определении ограничений полезно задавать их имена, которые обычно строятся по следующему правилу: в имя ограничения включается имя таблицы, затем условное название типа ограничения и номер ограничения данного типа, если их в данной таблице может быть несколько, или смысловое название ограничения.

Пример 2. Отношение связи ПОСТАВКА может быть определено следующим образом. Так как отношение ПОСТАВКА является отношением связи (дочерним отношением) между отношениями ПОСТАВЩИК и ДЕТАЛЬ (родительскими отношениями), прежде чем определять дочернее отношение, необходимо определить родительские.

Определение отношения ДЕТАЛЬ приведено выше.

Отношение ПОСТАВЩИК может быть определено следующим образом:

CREATE TABLE S(

S_ID SMALLINT NOT NULL,

SNAME VARCHAR (30) NOT NULL,

ADDRESS VARCHAR (80),

CONSTRAINT S_PK PRIMARY KEY (S_ID) -- 4

)

Пояснения:

4 – как говорилось выше, ограничение, накладываемое на одну колонку таблицы, может быть представлено и как ограничение на колонку, и как ограничение на таблицу. В определении таблицы P первичный ключ был определен при определении колонки (ограничение на колонку), а в определении данной таблицы первичный ключ определен как ограничение на таблицу.

Теперь определим отношение связи ПОСТАВКА:

CREATE TABLE SP(

S_ID SMALLINT NOT NULL CONSTRAINT SP_FK_01 REFERENCES S, --5

P_ID SMALLINT NOT NULL,

QTY INT NOT NULL CONSTRAINT SP_CH_01 CHECK (QTY > 0),

CONSTRAINT SP_PK PRIMARY KEY (S_ID, P_ID), --6

CONSTRAINT SP_FK_02 FOREIGN KEY (P_ID) REFERENCES P --7

)

Пояснения:

5 – определяется внешний ключ; в ограничении внешнего ключа (ключевое слово REFERENCES) указывается родительская таблица и ее первичный ключ; по умолчанию правило удаления определяется как NO ACTION.

6 – определяется ограничение первичного ключа; так как в данном примере первичный ключ составной, определение первичного ключа может быть задано только как ограничение уровня таблицы (ограничение внешнего ключа в данном примере, устанавливаемое только для одного атрибута, является ограничением уровня атрибута).

7 – также определяется внешний ключ, только в виде табличного ограничения. Имя колонки данной таблицы, являющейся внешним ключом, указывается после ключевых слов FOREIGN KEY; REFERENCES указывает родительскую таблицу; правило удаления также определяется как NO ACTION.

Удаление и модификация таблиц

С помощью предложений DROP TABLE и ALTER TABLE можно удалять существующие таблицы или изменять их структуру. В соответствующих предложениях должно быть указано имя существующей таблицы.

Удаление таблицы

Удаление таблицы осуществляется с помощью предложения DROP TABLE, имеющего следующий вид:

DROP TABLE имя_таблицы

При удалении таблицы удаляются все индексы и ограничения, определенные для данной таблицы.

Модификация таблицы

Модификация таблицы осуществляется с помощью предложения ALTER TABLE. Модификация таблицы предполагает изменение структуры таблицы; при этом можно добавлять новые колонки, изменять некоторые характеристики существующих колонок, а также добавлять новые и удалять существующие ограничения целостности. В одном предложении ALTER TABLE могут быть указаны несколько операций, изменяющих структуру таблицы.

В общем случае предложение ALTER TABLE имеет следующий вид:

ALTER TABLE имя_таблицы операция [ операция …]

Рассмотрим некоторые возможности, предоставляемые предложением ALTER TABLE.

Добавление новых колонок

Добавление новой колонки в таблицу выполняется с помощью следующей операции:

ADD [ COLUMN ] определение_колонки

Определение колонки имеет такой же вид, как и описание колонки, используемое в предложении

CREATE TABLE:

имя_новой_колонки тип_данных [ ограничение_обязательности ] [ ограничение_на_колонку ] [ значение_по_умолчанию ]

При добавлении новой колонки в таблицу необходимо обеспечить выполнение следующих условий:

• имя новой колонки не должно совпадать с именами колонок, уже существующих в таблице;

• если для новой колонки указано ограничение обязательности NOT NULL, должно быть обязательно задано значение по умолчанию с помощью WITH DEFAULT;

• для новой колонки не допускается указание автоинкрементного значения.

Пример. Пусть имеется таблица EQUIPMENT, в которой находится информация об оборудовании, выделенном для некоторых отделов предприятия. В эту таблицу необходимо добавить колонку с именем Equip_QTY для указания количества оборудования, предоставленного отделу. Значение данной колонки является обязательным; по умолчанию должно подставляться значение 1.

ALTER TABLE EQUIPMENT

ADD COLUMN Equip_QTY SMALLINT

NOT NULL WITH DEFAULT 1

или, что то же самое,

ALTER TABLE EQUIPMENT

ADD Equip_QTY SMALLINT

NOT NULL WITH DEFAULT 1

Изменение характеристик существующей колонки

С помощью предложения ALTER TABLE для существующей колонки можно (с определенными ограничениями) изменить тип данных и спецификации генерируемого значения.

Нельзя изменять характеристики колонки, которая была добавлена, удалена или изменена в этом же предложении ALTER TABLE.

Изменение типа данных существующей в таблице колонки выполняется с помощью следующей операции:

ALTER COLUMN имя_колонки SET DATA TYPE VARCHAR( целое )

При изменении типа данных колонки необходимо обеспечить выполнение следующих условий:

• колонка должна существовать в таблице;

• колонка должна иметь тип данных VARCHAR(n);

• существующее значение максимальной длины колонки не должно быть больше нового значения (т.е. должно выполняться условие n ≤ целое).

Пример. Пусть в таблице EQUIPMENT, упомянутой выше, имеется колонка Equip_Desc, объявленная как VARCHAR(20). Необходимо увеличить максимальную длину строки до 50 символов.

ALTER TABLE EQUIPMENT

ALTER COLUMN Equip_Desc SET DATA TYPE VARCHAR( 50 )

Удаление спецификации генерируемого значения выполняется с помощью следующих операций:

ALTER COLUMN имя_колонки DROP DEFAULT – удаляет установленное для колонки текущее значение по умолчанию;

ALTER COLUMN имя_колонки DROP IDENTITY – удаляет для указанной колонки установленный для нее атрибут автоинкрементного значения, делая колонку обычной колонкой целочисленного типа;

ALTER COLUMN имя_колонки DROP EXPRESSION – удаляет атрибут генерируемого значения для колонки.

Данные операции могут использоваться только для тех колонок, для которых установлены соответствующие спецификации генерируемого значения.

Установка спецификации генерируемого значения выполняется с помощью следующей операции:

ALTER COLUMN имя_колонки SET генерируемое_значение

Генерируемое значение для колонки задается точно так же, как и при создании таблицы в предложении CREATE TABLE. Если у колонки было установлено генерируемое значение, тип которого отличается от того, который задается в текущем предложении ALTER TABLE, исходное значение должно быть удалено; оно может быть удалено в этом же предложении с помощью операции удаления спецификации генерируемого значения, рассмотренной выше, например:

ALTER TABLE TAB_A

ALTER COLUMN Col_A DROP IDENTITY

ALTER COLUMN Col_A SET DEFAULT 123

Если в предложении устанавливаются спецификации автоинкрементного значения, должны быть выполнены следующие условия:

• в таблице может быть только одна автоинкрементная колонка;

• колонка должна быть определена с ограничением NOT NULL;

• тип данных колонки должен определять целочисленные значения, т.е. это может быть один из типов данных: SMALLINT, INTEGER, BIGINT, DECIMAL или NUMERIC с масштабом, равным 0.

Новые установленные значения не влияют на значения колонки, уже существующие в таблице; они начинают действовать только для операций вставки в таблицу, выполняющихся после выполнения соответствующего предложения ALTER TABLE.

Пример. Изменить в таблице EMPLOYEE для колонки WorkDept значение по умолчанию на 123.

ALTER TABLE EMPLOYEE

ALTER COLOMN WorkDept SET DEFAULT '123'

Изменение спецификации генерирующего выражения для колонки выполняется с помощью следующей операции:

ALTER COLUMN имя_колонки SET EXPRESSION AS (выражение)

Указанная колонка должна иметь установленную спецификацию генерирующего выражения. Новое выражение задается по тем же правилам, которые используются при создании таблицы в предложении CREATE TABLE. Данная операция требует выполнения обязательных дополнительных действий, описание которых можно найти в SQL Reference.

Изменение спецификации автоинкрементного значения для колонки выполняется с помощью следующей операции:

ALTER COLUMN имя_колонки изменение_спецификаций_identity

Указанная колонка должна иметь установленные спецификации автоинкрементного значения. Изменение спецификаций identity задается следующим образом:

RESTART [ WITH числовая_константа ] – предписывает начать генерацию новой последовательности значений с указанного числовой_константой значения; если оно опущено, последовательность значений начинает формироваться с того значения, которое было указано при первом определении спецификаций автоинкрементного значения для данной колонки. Данная операция не изменяет исходное начальное значение последовательности, определенное с помощью START WITH.

SET свойство [ значение ] – указывает новые значения для заданных спецификаций автоинкрементного значения, например:

SET INCREMENT BY числовая_константа

или