Принципы поддержки целостности в реляционной модели данных

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

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

определение таблицы>::=CREATE TABLE <имя таблицы>
(описание элемента таблицы> [{,<описание элемента таблицы>}...])
<описание элемента таблицы>::=<определение столбца>
определение ограничений таблицы>
определение столбца>::=<имя столбца> <тип данных>
[<значение по умолчанию>][<дополнительные ограничения столбца>...]
<значение по умолчанию>::=DEFAULT { <literal> | 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 (C IS NOT NULL)" (где C — имя данного столбца). Если ограничение NOT NULL не указано и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая спецификация уникальности для таблицы.

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

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

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

В "Язык SQL. Формирование запросов к базе данных" определены типы данных, которые допустимы по стандартам 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 >= 1960 AND
      YEAR_PUBL <= YEAR(GetDate())),
  PUBLICH  varchar(20)  NULL,
  PAGES    smallint CHECK(PAGES > = 5 AND PAGES <= 1000) );

Почему мы не задали обязательность значения для количества страниц в книге? Потому что это является следствием проверочного ограничения, заданного на количество страниц, количество страниц всегда должно лежать в пределах от 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_DAY  date 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 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 ) и порядковый номер экземпляра данной книги ( ID_EXEMPL ), в этом случае оператор создания таблицы EXEMPLAR будет выглядеть следующим образом:

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) );

Мы видим, что один и тот же атрибут ISBN, с одной стороны, является внешним ключом ( FOREIGN 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 smallint DEFAULT Year(GetDate()) CHECK(YEAR_PUBL >= 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, после которого следует уникальное имя ограничения, затем тип ограничения и его выражения. Для идентификации ограничений рекомендуют использовать систему именования, которая легко позволит определить при получении сообщения об ошибке, которое вырабатывает СУБД, какое ограничение нарушено. Обычно имя ограничения состоит из краткого названия типа ограничения, далее через символ подчеркивания идет имя атрибута или таблицы, в зависимости от того, к какому уровню относится ограничение, и, наконец, порядковый номер ограничения данного типа, если к одному объекту задается несколько ограничений одного типа.

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

• 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 <= YEAR(GetDate())), 
    CONSTRAINT 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  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   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 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 smallint    NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG), 
    CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN,ID_CATALOG) 
).

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

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

1. Таблица BOOKS
2. Таблица READERS
3. Таблица CATALOG (системный каталог)
4. Таблица EXEMPLAR
5. Таблица 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 <= 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    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 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 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 smallint   NOT NULL FOREIGN KEY references CATALOG(ID_CATALOG),
   CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN,ID_CATALOG)
).

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