Теория баз данных
Операторы DDL в языке SQL с заданием ограничений целостности
Декларативные ограничения целостности задаются на уровне операторов создания таблиц. В стандарте SQL оператор создания таблиц имеет следующий синтаксис:
определение таблицы>: :=CREATE TABLE <имя таблицы>
(<описание элемента таблицы> [{.<описание элемента таблицы>}...])
<описание элемента таблицы>::=<определение столбца>|
<определение ограничений таблицы>
определение столбца>::=<имя столбца> <тип данных>
[<значение по умолчанию>][<дополнительные ограничения столбца>...]
<значение по умолчанию>::=DEFAULT { <litera1> | USER | NULL }
дополнительные ограничения столбца>: :=NOT NULL
[ограничение уникальности столбца>]|
<ограничение по ссылкам столбца>|
CHECK (<условия проверки на допустимость>) ограничение уникальности столбца>::= UNIQUE
<ограничение по ссылкам столбца>: :=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. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.
При задании ограничений уникальности данный столбец определяется как возможный ключ, что предполагает уникальность каждого вводимого значения в данный столбец. И если это ограничение задано, то СУБД будет автоматически осуществлять проверку на отсутствие дубликатов значений данного столбца во всей таблице.
Если в. разделе ограничений целостности указано ограничение по ссылкам данного столбца, то порождается соответствующее определение ограничения по ссылкам для таблицы: FOREIGN КЕУ(<имя столбца>) <спецификация ссылки>, что означает, что значения данного столбца должны быть взяты из соответствующего столбца родительской таблицы.
Родительской таблицей в данном случае называется таблица, которая связана с данной таблицей связью «один-ко-многим» (1:М). При этом каждая строка родительской таблицы может быть связана с несколькими строками определяемой таблицы. Трансляция операторов SQL проводится в режиме интерпретации, поэтому важно, чтобы сначала была бы описана родительская таблица, а потом уже все подчиненные (дочерние) таблицы, связанные с ней. Иначе транслятор определит ссылку на неопределенный объект.
Наконец, если указано проверочное ограничение столбца, то условие поиска этого ограничения должно ссылаться только на данный столбец, и неявно порождается соответствующее проверочное ограничение для всей таблицы. В проверочных ограничениях, накладываемых на столбец, нельзя задавать сравнение со значениями других столбцов данной таблицы.
В главе 5 определены типы данных, которые допустимы по стандартам SQL. Попробуем написать простейший оператор создания таблицы 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_PUBLsmallint DEFAULT Year(GetDate())
CHECK(YEAR_PUBL >= 1960 AND YEAR PUBL <= YEAR(GetDate())),
PUBLICH varchar(20) NULL.
PAGES smalllnt CHECK(PAGES > = 5 AND PAGES <= 1000)
);
Почему мы не задали обязательность значения для количества страниц в книге? Потому что это является следствием проверочного ограничения, заданного на количество страниц, количество страниц всегда должно лежать в пределах от 5 до 1000, значит, оно не может быть незаданным и система это контролирует автоматически.
Теперь зададим описание таблицы «Читатели», которой соответствует отношение READERS:
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 chart 12),
BIRTH_DAYdate
CHECK(DateDiff(year. GetDate() ,BIRTH_DAY) >=17)
);
Здесь DateDiff (часть даты, начальная дата, конечная дата) — функция MS SQL Server 7.0, которая определяет разность между начальной и конечной датами, заданную в единицах, определенных первым параметром — часть даты. Мы задали в качестве параметра Year, что значит, что мы разность определяем в годах.
Теперь зададим операцию создания таблицы EXEMPLAR (экземпляры книги). В этой таблице первичным ключом является атрибут, задающий инвентарный номер экземпляра книги. В такой постановке мы полагаем, что при поступлении книг в библиотеку им просто присваиваются соответствующие порядковые номера. Для того чтобы не утруждать библиотекаря все время помнить, какой номер был последним, мы можем воспользоваться тем, что некоторые СУБД допускают специальный инкрементный тип данных, то есть такой, значения которого автоматически увеличиваются или уменьшаются на заданную величину при каждом новом вводе данных.
В СУБД MS Access такой тип данных называется «счетчик» (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 | NT IDENTITY PRIMARY KEY,
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN),
READERJD Smallint(4) NULL FOREIGN KEY references READERS (READERJD).
DATA_IN date.
DATA_OUT date,
EXIST Logical,
);
Как мы уже знаем, не все декларативные ограничения могут быть заданы на уровне столбцов таблицы, часть ограничений может быть задана только на уровне всей таблицы. Например, если мы имеем в качестве первичного ключа не один атрибут, а последовательность атрибутов, то мы не можем определить ограничение типа PRIMARY KEY (первичный ключ) только на уровне всей таблицы.
Допустим, что мы считаем экземпляры книги не подряд, а отдельно для каждого издания, тогда таблица EXEMPLAR в качестве первичного ключа будет иметь набор из двух атрибутов: это шифр книги (ISBN) и порядковый номер экземпляра
данной книги (ID_EXEMPL), в этом случае оператор создания таблицы EXEMPLAR будет выглядеть следующим образом:
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR int NOT NULL.
ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN),
READERJD Smallint(4) NULL FOREIGN KEY references READERS (READERJD),
DATA_IN date.
DATA_OUT date,
EXIST Logical.
PRIMARY KEY (ID_EXEMPLAR. 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.
YEARJHJBL small int DEFAULT Year(GetDate())
CHECK(YEARJ>UBL > 1960 AND YEAR_PUBL <= YEAR(GetDate())).
PUBLICH varchar(20) NULL.
PAGES smallint CHECK( PAGES >= 5 AND PAGES <= 1000).
CHECK (NOT ( AUTOR IS NULL AND COAUTOR IS NOT NULL))
);
Для анализа ошибок целесообразно именовать все ограничения, особенно если таблица содержит несколько ограничений одного типа. Для именования ограничений используется ключевое слово CONSTRAINT, после которого следует уникаль-
ное имя ограничения, затем тип ограничения и его выражения. Для идентификации ограничений рекомендуют использовать систему именования, которая легко позволит определить при получении сообщения об ошибке, которое вырабатывает СУБД, какое ограничение нарушено. Обычно имя ограничения состоит из краткого названия типа ограничения, далее через символ подчеркивания идет имя атрибута или таблицы, в зависимости от того, к какому уровню относится ограничение, и, наконец, порядковый номер ограничения данного типа, если к одному объекту задается несколько ограничений одного типа.
Сокращенные обозначения ограничений состоят из одной или двух букв и могут быть следующими:
Приведем пример оператора создания таблицы 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 OF_ YEAR_PUBL DEFAULT (Year(GetDate()).
CONSTRAINT CK_ YEAR_PUBL CHECK (YEAR_PUBL >- 1960 AND
YEAR_PUBL <= YEAR(GetDate())).
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES <= 1000).
CONSTRAINT CK_BOOKS CHECK (NOT ( AUTOR IS NULL AND COAUTOR IS NOT NULL))
);
CREATE TABLE READERS
(
|
READER_ID |
Small int |
PRIMARY KEY |
||
|
FIRST_NAME |
char(30) |
NOT NULL. |
||
|
LAST_NAME |
char(30) |
NOT NULL. |
||
|
ADRES |
char(50). |
|
||
WORK_PHON char(12).
BIRTH_DAY date CHECK( 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 REABERS (READER_ID).
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 накладывает ограничение на порядок описания создаваемых таблиц. Действительно, если при трансляции оператора описания подчиненной таблицы с указанным внешним ключом и соответствующей ссылкой на родительскую таблицу эта родительская таблица не будет обнаружена, то мы получим сообщение об ошибке с указанием ссылки на несуществующий объект. Сначала должны быть описаны все основные таблицы, а потом подчиненные таблицы.
В нашем примере с библиотекой порядок описания таблиц следующий:
Набор операторов языка 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 <= YEAR(GetDate())).
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES <= 1000).
CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL)) CREATE TABLE READERS
(
READER_ID Small int 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 СНЕ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(l50)
);
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 (READER_ID).
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 small int NOT NULL
FOREIGN KEY references CATALOG (ID_CATALOG),
CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN.ID_CATALOG)
);
При написании скрипта мы добавили в оператор создания таблицы «Читатели» ограничение на уровне таблицы, которое связано с обязательным наличием хотя бы одного из двух телефонов.
