Операторы DDL в языке SQL с заданием ограничений целостности

Декларативные ограничения целостности задаются на уровне операторов создания таблиц. В стандарте SQL оператор создания таблиц имеет следующий синтаксис:

определение таблицы>::=CREATE TABLE

( [{.}...])

::=|

::=

[][...]

::=DEFAULT { | USER | NULL }

::=NOT NULL

[]|

|

CHECK ()

::= UNIQUE

140

: :=FOREIGN KEY

.:= REFERENCES

()

Давайте кратко прокомментируем оператор определения таблицы, синтаксис которого мы задали с помощью традиционной формы Бэкуса - Наура.

При описании таблицы задается имя таблицы, которое является идентификатором в базовом языке СУБД и должно соответствовать требованиям именования объектов в данном языке.

Кроме имени таблицы в операторе указывается список элементов таблицы, каждый из которых служит либо для определения столбца, либо для определения ограничения целостности определяемой таблицы. Требуется наличие хотя бы одного определения столбца. То есть таблицу, которая не имеет ни одного столбца, определить нельзя. Количество столбцов в одной таблице не ограничено, но в конкретных СУБД обычно бывают ограничения на количество атрибутов. Так, например, в MS SQL Server 6.5 максимальное количество столбцов в таблице было 250, но уже в MS SQL Server 7.0 оно увеличено до 1024.

Оператор CREATE TABLE определяет так называемую базовую таблицу, то есть реальное хранилище данных.

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

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


Если значение столбца по умолчанию не специфицировано и в разделе ограничений целостности столбца указано NOT NULL (то есть наличие неопределенных значений запрещено), то попытка занести в таблицу строку с незаданным значением данного столбца приведет к ошибке.

Задание в разделе ограничений целостности столбца выражения NOT NULL приводит к неявному порождению проверочного ограничения целостности для всей таблицы "CHECK (С IS NOT NULL)" (где С - имя данного столбца). Если ограничение NOT NULL не указано и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.

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

141

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

Если в разделе ограничений целостности указано ограничение по ссылкам данного столбца, то порождается соответствующее определение ограничения по ссылкам для таблицы: FOREIGN KEY() , что означает, что значения данного столбца должны быть взяты из соответствующего столбца родительской таблицы. Родительской таблицей в данном случае называется таблица, которая связана с данной таблицей связью "один - ко - многим" (1:М). При этом каждая строка родительской таблицы может быть связана с несколькими строками определяемой таблицы. Трансляция операторов SQL проводится в режиме интерпретации, поэтому важно, чтобы сначала была бы описана родительская таблица, а потом уже все подчиненные (дочерние) таблицы, связанные с ней. Иначе транслятор определит ссылку на неопределенный объект.

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


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

В главе 5 определены типы данных, которые допустимы по стандартам SQL. Попробуем написать простейший оператор создания таблицы BOOKS из базы данных "Библиотека".

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

Шифр книги - последовательность символов длиной не более 14, однозначно определяющая книгу, значит, это - фактически первичный ключ таблицы BOOKS.

Название книги - последовательность символов, не более 120. Обязательно должно быть задано.

Автор - последовательность символов, не более 30, может быть не задан.

Соавтор - последовательность символов, не более 30, может быть не задан.

Год издания - целое число, не менее 1960 и не более текущего года. По умолчанию ставится текущий год.

Издательство - последовательность символов, не более 20, может отсутствовать.

Количество страниц - целое число не менее 5 и не более 1000.

CREATE TABLE BOOKS

(

ISBN varchar(14) NOT NULL PRIMARY KEY,

TITLE varchar(120) NOT NULL.

AUTOR varchar (30) NULL.

COAUTOR varchar(30) NULL.

YEAR_PUBL smallint DEFAULT Year(GetDate( )) CHECK(YEAR_PUBL >= I960 AND

YEAR PUBL

142

PUBLICH varchar(20) NULL,

PAGES smallint CHECK(PAGES > - 5 AND PAGES

):

Почему мы не задали обязательность значения для количества страниц в книге? Потому что это является следствием проверочного ограничения, заданного на количество страниц, количество страниц всегда должно лежать в пределах от 5 до 1000, значит, оно не может быть незаданным и система это контролирует автоматически.

Теперь зададим описание таблицы "Читатели", которой соответствует отношение READERS:

Номер читательского билета - это целое число в пределах 32 000 и он уникально определяет читателя.

Имя, фамилия читателя - это последовательность символов, не более 30.

Адрес - это последовательность символов, не более 50.

Номера телефонов рабочего и домашнего - последовательность символов, не более 12.

Дата рождения - календарная дата. В библиотеку принимаются читатели не младше 17 лет.

CREATE TABLE READERS

(

READER_ID Smallint(4) PRIMARY KEY,

FIRST_NAME char(30) NOT NULL,

LAST_NAME char(30) NOT NULL,

ADRES . char(50).

HOME_PHON char(12).

WORK_PHON char(12).

BIRTH_DAYdate CHECK(DateDiff(year, GetDate( ).BIRTH_DAY) >=17)

):

Здесь DateDiff (часть даты, начальная дата, конечная дата) - функция MS SQL Server 7.0, которая определяет разность между начальной и конечной датами, заданную в единицах, определенных первым параметром - часть даты. Мы задали в качестве параметра Year, что значит, что мы разность определяем в годах.

Теперь зададим операцию создания таблицы EXEMPLAR (экземпляры книги). В этой таблице первичным ключом является атрибут, задающий инвентарный номер экземпляра книги. В такой постановке мы полагаем, что при поступлении книг в библиотеку им просто присваиваются соответствующие порядковые номера. Для того чтобы не утруждать библиотекаря все время помнить, какой номер был последним, мы можем воспользоваться тем, что некоторые СУБД допускают специальный инкрементный тип данных, то есть такой, значения которого автоматически увеличиваются или уменьшаются на заданную величину при каждом новом вводе данных. В СУБД MS Access такой тип данных называется

143

"счетчик" (counter) и он всегда имеет начальное значение 1 и шаг, равный тоже 1, то есть при вводе каждого нового значения счетчик увеличивается на 1, значит, практически считает вновь введенные значения. В СУБД MS SQL Server 7.0 это свойство IDENTITY, которое может быть присвоено ряду целочисленных типов данных. В отличие от "счетчика" свойство IDENTITY позволяет считать с любым шагом, положительным или отрицательным, но обязательно целым. Если мы не задаем дополнительных параметров этому свойству, то оно начинает работать как счетчик в MS Access, начиная с единицы и добавляя при каждом вводе тоже единицу.

Кроме того, таблица EXEMPLAR является подчиненной двум другим ранее определенным таблицам: BOOKS и READERS.


При этом с таблицей BOOKS таблица EXEMPLAR связана обязательной связью, потому что не может быть ни одного экземпляра книги, который бы не был приписан конкретной книге. С таблицей READERS таблица EXEMPLAR связана необязательной связью, потому что не каждый экземпляр в данный момент находится на руках у читателя. Для моделирования этих связей при создании таблицы EXEMPLAR должны быть определены два внешних ключа (FOREIGN KEY). При этом атрибут, соответствующий шифру книги (мы его назовем так же, как и в родительской таблице - ISBN), является обязательным, то есть не может принимать неопределенных значений, а атрибут, который является внешним ключом для связи с таблице READERS, является необязательным и может принимать неопределенные значения.

Необязательными там являются два остальных атрибута: дата взятия и дата возврата книги, оба они имеют тип данных, соответствующей календарной дате. Атрибут, который содержит информацию о присутствии или отсутствии книги, имеет логический тип. Напишем оператор создания таблицы EXEMPLAR в синтаксисе MS SQL Server 7.0:

CREATE TABLE EXEMPLAR

(

EXEMPLAR_ID INT IDENTITY PRIMARY KEY.

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN),

READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READER_ID),

DATA_IN date,

DATA_OUT date,

EXIST Logical.

):

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

Допустим, что мы считаем экземпляры книги не подряд, а отдельно для каждого издания, тогда таблица EXEMPLAR в качестве первичного ключа будет иметь набор из двух атрибутов: это шифр книги (ISBN) и порядковый номер экземпляра

144

данной книги (ID_EXEMPL), в этом случае оператор создания таблицы EXEMPLAR будет выглядеть следующим образом:

CREATE TABLE EXEMPLAR

(

ID_EXEMPLAR int NOT NULL.

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).

READERJD Small int (4) NULL FOREIGN KEY references READERS (READERJD).

DATA_IN date.

DATA_OUT date.

EXIST Logical.

PRIMARY KEY (IDJXEMPLAR. ISBN)

);

Мы видим, что один и тот же атрибут ISBN, с одной стороны, является внешним ключом (FORIGN KEY), а с другой стороны, является частью первичного ключа (PRIMARY KEY). И ограничение типа первичный ключ (PRIMARY KEY) задается не на уровне одного атрибута, а на уровне всей таблицы, потому что оно содержит набор атрибутов.

То же самое можно сказать и о проверочных (CHECK) ограничениях, если условия проверки предполагают сравнения значений нескольких столбцов таблицы. Введем дополнительное ограничение для таблицы BOOKS, которое может быть сформулировано следующим образом: соавтор не может быть задан, если не задан автор. При описании книги допустимо не задавать ни автора, ни соавтора, или задать и автора и соавтора, или задать только автора. Однако задание соавтора в отсутствие задания автора считается ошибочным. В этом случае оператор создания таблицы BOOKS будет выглядеть следующим образом:

CREATE TABLE BOOKS

(

ISBN varchar(14) NOT NULL PRIMARY KEY.

TITLE varchar(120) NOT NULL.

AUTOR varchar (30) NULL.

COAUTOR varchar(30) NULL.

YEAR_PUBL small int DEFAULT Year(GetDate( )) CHECK(YEAR_PUBL >= I960 AND

YEAR_PUBL

PUBLICH varchar(20) NULL.

PAGES smallint CHECK(PAGES >= 5 AND PAGES

CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))

);

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

145

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


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

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

PK - для первичного ключа;

FK - для внешнего ключа;

CK - для проверочного ограничения;

U - для ограничения уникальности;

DF - для ограничения типа значение по умолчанию.

Приведем пример оператора создания таблицы BOOKS с именованными ограничениями:

CREATE TABLE BOOKS

(

ISBN varchar(14) NOT NULL,

TITLE varchar(120) NOT NULL,

AUTOR varchar (30) NULL.

COAUTOR varchar(30) NULL.

YEAR_PUBL smallint NOT NULL.

PUBLICH varchar(20) NULL.

PAGES smallint NOT NULL.

CONSTRAINT PK_BOOKS PRIMARY KEY (ISBN).

CONSTRAINT DF_ YEAR_PUBL DEFAULT (Year(GetDate( )).

CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >= 1960 AND

YEAR_PUBL

CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES

CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))

):

CREATE TABLE READERS

(

READER_ID Smallint PRIMARY KEY.

FIRST_NAME char(30) NOT NULL.

LAST_NAME char(30) NOT NULL.

ADRES char(50).

146

HOME_PHON char(12).

WORK_PHON char(12),

BIRTH_DAY date СНЕCK DateDiff(year. GetDate().BIRTH_DAY) >=17 ).

CONSTRAINT CK_READERS CHECK (HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL)

);

CREATE TABLE CATALOG

(

ID_CATALOG Smallint PRIMARY KEY,

KNOWELEDGE_AREA varchar(150)

);

CREATE TABLE EXEMPLAR

(

ID_EXEMPLAR int NOT NULL,

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).

READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READERJD).

DATA_IN date.

DATA_OUT date,

EXIST Logical,

PRIMARY KEY (ID_EXEMPLAR, ISBN)

);

CREATE TABLE RELATION_1

(

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).

ID_CATALOG smallint NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG).

CONSTRAINT PK RELATION 1 PRIMARY KEY (ISBN,ID CATALOG)

).

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

В нашем примере с библиотекой порядок описания таблиц следующий:

147

Таблица BOOKS

Таблица READERS

Таблица CATALOG (системный каталог)

Таблица EXEMPLAR

Таблица RELATION_1 (дополнительная связующая таблица между книгами и системным каталогом).

Набор операторов языка SQL принято называть не программой, а скриптом. Тогда скрипт, который добавит набор из 5 взаимосвязанных таблиц базы данных "Библиотека" в существующую базу данных, будет выглядеть следующим образом:

CREATE TABLE BOOKS

(

ISBN varchar(14) NOT NULL ,

TITLE varchar(120) NOT NULL.

AUTOR varchar (30) NULL.

COAUTOR varchar(30) NULL.

YEAR_PUBL smallint NOT NULL.

PUBLICH varchar(20) NULL.

PAGES smallint NOT NULL.

CONSTRAINT PK_BOOKS PRIMARY KEY (ISBN).

CONSTRAINT DF_ YEAR_PUBL DEFAULT (Year(GetDate( )).

CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >= 1960 AND

YEAR_PUBL

CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES

CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))

CREATE TABLE READERS

(

READER_ID Smallint PRIMARY KEY.

FIRST_NAME char(30) NOT NULL.

LAST_NAME char(30) NOT NULL.

ADRES char(50),

HOME_PHON char(12).

WORK_PHON char(12),

BIRTH_DAY date СНЕCK DateDiff(year. GetDate(),BIRTH_DAY) >=17 ).

CONSTRAINT CK_READERS CHECK ( HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL)

);

CREATE TABLE CATALOG

(

148

ID_CATALOG Smallint PRIMARY KEY.

KNOWELEDGE_AREA varchar(lSO)

);

CREATE TABLE EXEMPLAR

(

ID_EXEMPLAR Int NOT NULL,

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).

READER_ID Smallint(4) NULL FOREIGN KEY references READERS (READERJD),

DATA_IN date,

DATA_OUT date,

EXIST Logical,

PRIMARY KEY (ID_EXEMPLAR. ISBN)

):

CREATE TABLE RELATION_1

(

ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN).

ID_CATALOG smallint NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG).

CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN.ID_CATALOG)

).

При написании скрипта мы добавили в оператор создания таблицы "Читатели" ограничение на уровне таблицы, которое связано с обязательным наличием хотя бы одного из двух телефонов.

Содержание раздела

---

---