Пример операторов создания таблиц БД «Библиотека»
Будем предполагать наличие следующих ограничений целостности для таблицы BOOKS:
- Шифр книги — последовательность символов длиной не более 14, однозначно определяющая книгу, значит, это — фактически первичный ключ таблицы BOOKS.
- Название книги — последовательность символов, не более 120. Обязательно должно быть задано.
- Автор — последовательность символов, не более 30, может быть не задан.
- Соавтор — последовательность символов, не более 30, может быть не задан.
- Год издания — целое число, не менее 1960 и не более текущего года. По умолчанию ставится текущий год.
- Издательство — последовательность символов, не более 20, может отсутствовать.
- Количество страниц — целое число не менее 5 и не более 1000.
Теперь зададим описание таблицы "Читатели", которой соответствует отношение READERS:
- Номер читательского билета — это целое число в пределах 32К, уникальное.
- Имя, фамилия читателя — это последовательность символов, не более 30.
- Адрес — это последовательность символов, не более 250.
- Номера телефонов рабочего и домашнего — последовательность символов, не более 12.
- Дата рождения — дата. В библиотеку принимаются читатели не младше 17 лет.
Здесь DateDiff (часть даты, начальная дата, конечная дата) — функция MS SQL Server, которая определяет разность между начальной и конечной датами, заданную в единицах, определенных первым параметром — часть даты. Мы задали в качестве параметра Year, что значит, что мы разность определяем в годах.
Теперь зададим операцию создания таблицы EXEMPLAR (экземпляры книги). В этой таблице первичным ключом является атрибут, задающий инвентарный номер экземпляра книги. В СУБД MS SQL Server есть свойство IDENTITY, которое может быть присвоено ряду целочисленных типов данных. В отличие от "счетчика" (например, в MS Access) свойство IDENTITY позволяет считать с любым шагом, положительным или отрицательным, но обязательно целым. Если мы не задаем дополнительных параметров этому свойству, то оно начинает работать как счетчик в MS Access, начиная с единицы и добавляя при каждом вводе тоже единицу.
Кроме того, таблица EXEMPLAR является подчиненной двум другим ранее определенным таблицам: BOOKS и READERS. При этом с таблицей BOOKS таблица EXEMPLAR связана обязательной связью, потому что не может быть ни одного экземпляра книги, который бы не был приписан конкретной книге. С таблицей READERS таблица EXEMPLAR связана необязательной связью, потому что не каждый экземпляр в данный момент находится на руках у читателя.
Оператор создания таблицы EXEMPLAR в синтаксисе MS SQL Server:
CREATE TABLE EXEMPLAR ( EXEMPLAR_ID INT IDENTITY PRIMARY KEY, ISBN varchar(14) NOT NULL FOREIGN KEY references BOOKS(ISBN), READER_ID Smallint(2) NULL FOREIGN KEY references READERS (READER_ID), DATA_IN date, DATA_OUT date, EXIST Logical)Как мы уже знаем, не все декларативные ограничения могут быть заданы на уровне столбцов таблицы, часть ограничений может быть задана только на уровне всей таблицы. Например, если мы имеем в качестве первичного ключа не один атрибут, а последовательность атрибутов, то мы не можем определить ограничение типа PRIMARY KEY только на уровне всей таблицы.
Допустим, что мы считаем экземпляры книги не подряд, а отдельно для каждого издания, тогда таблица EXEMPLAR в качестве первичного ключа будет иметь набор из двух атрибутов: это шифр книги (ISBN) и порядковый номер экземпляра данной книги (ID_EXEMPL):
CREATE TABLE EXEMPLAR (ID_EXEMPL 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_EXEMPL, ISBN) )То же самое можно сказать и о проверочных (CHECK) ограничениях, если условия проверки предполагают сравнения значений нескольких столбцов таблицы. Введем дополнительное ограничение для таблицы 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), PHON char(12), WORK_PHON char(12), BIRTH_DAY date CHECK( DateDiff(year, GetDate(),BIRTH_DAY) >=17 ), CONSTRAINT CK_READERS CHECK (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 BOOKS2CATALOG ( 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, как указывалось ранее, транслируются в режиме интерпретации, в отличие от большинства алгоритмических языков, трансляторы для которых выполнены по принципу компиляции. Поэтому сначала должны быть описаны все основные таблицы, а потом подчиненные таблицы. В нашем примере с библиотекой порядок описания таблиц следующий: