Язык определения данных

ЛЕКЦИЯ № 12 4. ЯЗЫК ОПРЕДЕЛЕНИЯ ДАННЫХ...............................................................1 4.1. Домены........................................................................2 4.2. Создание, изменение и удаление базовых таблиц БД 5 4.2.1. Создание таблицы..............................................5 4.2.2. Определение ограничений столбца................10 4.2.3. Определение ограничений на таблицу...........16 4.2.5. Изменение определения таблицы...................20 4. ЯЗЫК ОПРЕДЕЛЕНИЯ ДАННЫХ Для создания и изменения структуры объектов БД предназначены SQLзапросы, называемые языком определения данных, или DDL (Data Definition Language). В различных СУБД такие запросы предоставляют примерно одинаковый набор возможностей, при этом набор объектов БД может несколько отличаться. В общем случае с помощью DDL-запросов можно:  определить структуру нового домена и создать его (из рассматриваемых СУБД – только в Firebird);  определить структуру новой таблицы и создать ее;  изменить определение существующей таблицы;  определить виртуальную таблицу (представление, курсор);  создать индексы для ускорения доступа к таблицам;  удалить существующий объект БД и т. д. Ядро языка определения данных образуют следующие три основных SQLзапроса:  CREATE (создать), позволяющий определить и создать объект БД;  DROP (удалить), служащий для удаления существующего объекта БД;  ALTER (изменить), с помощью которого можно изменить определение объекта БД. В некоторых СУБД могут использоваться дополнительно запросы:  RECREATE (заново создать), с помощью которого можно заново создать объект со старым именем;  CREATE OR ALTER (создать или изменить), позволяющий определить новый либо переопределить существующий объект БД. Запросы RECREATE и CREATE OR ALTER могут использоваться в Firebird, запрос RECREATE также доступен для Oracle. Пустая БД представляет собой файл (или набор файлов), который не содержит таблиц пользователя, но содержит системные таблицы. Есть два основных способа создать БД: • воспользоваться графическим помощником утилиты для работы с БД; • выполнить скрипт в командном режиме. Графические помощники утилит для работы с БД различных СУБД позволяют легко создавать, модифицировать и удалять БД. Обычно 1 помощник умеет создавать как БД с типичными предопределенными параметрами (в этом случае вводить вручную почти ничего не требуется), так и нестандартную БД (придется выбирать из нескольких вариантов и отвечать на дополнительные вопросы). При работе с Firebird, например, физическая структура пустой БД (файл) может быть создана в интерактивном режиме IBExpert. Метод создания БД с помошью скрипта заключается в том, чтобы написать новый или отредактировать имеющийся SQL-сценарий, содержащий команду CREATE DATABASE. Сегодня большинство пользователей предпочитают создавать БД с помощью стандартного инсталлятора. Для подключения к существующей БД можно воспользоваться соответствующим пунктом меню утилиты или использовать скрипт, содержащий команду CONNECT. Итак, чтобы создать БД, например в Firebird, нужно: 1) создать файл БД и выполнить команду подключения к базе; 2) создать необходимые домены; 3) создать пользовательские таблицы; 4) заполнить данными созданные таблицы пользователя. Команды CREATE DATABASE и CONNECT будут рассмотрены при изучении SQL-скриптов (см. 6.1.1). В настоящей главе рассматриваются задачи определения доменов, создания, изменения и удаления постоянных и временных пользовательских таблиц. Дается понятие индексов и описывается их роль в повышении эффективности выполнения операторов SQL. Здесь же рассмотрены запросы создания и изменения индексов. Все это относится к самим таблицам, а не к данным, которые в них содержатся. Также в данной главе приводится описание правил создания представлений различных видов, используемых для ограничения доступа отдельных пользователей к различным данным. Кратко описывается ряд объектов БД, специфичных для конкретных СУБД. 4.1. ДОМЕНЫ Из СУБД, рассматриваемых в настоящем учебнике, домены как объект БД реализованы только в Firebird. Если в таблицах БД имеются столбцы, обладающие одними и теми же характеристиками, то можно предварительно определить с помощью домена тип данных и поведение таких столбцов, а затем поставить в соответствие каждому из однотипных столбцов имя соответствующего домена. Доменом называется именованное множество скалярных значений одного типа. Например, домен TMonth (месяц) в учебной БД – это множество всех возможных номеров месяцев (от 1 до 12). Цель доменов в SQL – обеспечить возможность однажды определить элементарную спецификацию типа 2 данных, а затем использовать ее одновременно для нескольких столбцов в нескольких базовых таблицах. Домен – это тип данных, как это понимается в современных языках программирования. Например, в языке программирования Pascal допустимы следующие выражения: Type TDay = (Sun, Mon, Tue, Wed, Thu, Fri, Sat); Var Today : TDay; Здесь имеются определенный пользователем тип TDay (имеющий в точности 7 допустимых значений) и переменная Today, принадлежащая этому типу данных (и ограниченная этими семью значениями). Эта ситуация аналогична ситуации в реляционной БД, когда имеются домен, названный TDay, и атрибут, названный Today. Основной особенностью доменов является то, что домены ограничивают сравнения. Это значит, что сравнение атрибутов, определенных на основе одного домена, имеет смысл, а сравнение атрибутов, определенных на основе различных доменов, бессмысленно. Домены имеют концептуальную природу. Они должны быть определены в рамках конкретной БД. Тогда каждое определение атрибута (например, столбца определенной таблицы) должно включать ссылку на соответствующий домен. Таким образом, системе будет известно, какие атрибуты можно сравнивать, а какие – нет. Синтаксис запроса определения домена имеет вид: CREATE DOMAIN имя_домена [AS] <тип_данных> [DEFAULT { литерал | NULL }] [NOT NULL] [CHECK ( <ограничение_домена>)];, где имя_домена – имя создаваемого домена; <тип_данных> – тип данных, представленный в табл. 2.5; DEFAULT– ключевое слово, определяющее значение по умолчанию, применяемое к каждому столбцу:  литерал – любая самоопределенная константа строкового, числового типа или типа дата/время (соответствует типу данных домена), предварительно определенный литерал или контекстная переменная;  NULL – неопределенное (неизвестное) значение. Существует набор ограничений доменной целостности к каждому столбцу, определенному на этом домене:  NOT NULL – запрещает неопределенные значения. Атрибут используется при определении домена, если требуется, чтобы все столбцы, создаваемые на основе этого домена, не имели пустого значения. Переопределить атрибут NOT NULL, заданный для домена, нельзя. Часто неизвестно, действительно ли потребуется, чтобы все столбцы, определяемые на домене, имели NOT NULL значения. Также часто требуется определять внешний ключ на домене без условия NOT NULL. Поэтому предпочтительнее добавлять атрибут NOT NULL декларативно, т. е. при определении столбцов (см. разд. 4.2.1);  CHECK – определяет список ограничений на значения (VALUE) в соответствующем столбце. Ограничение домена фактически повторяет синтаксис условия поиска в секции WHERE для однотабличных запросов 3 (используется простое сравнение, проверка на принадлежность диапазону, на членство во множестве и т. п.) с той лишь разницей, что вместо точного указания проверяемого значения используется слово VALUE. Ограничение домена может быть одним из следующих: <ограничение_домена>::= [NOT] <ограничение_домена1> [[AND|OR][NOT] <ограничение_домена2>]…, где <ограничение_домена>::= {VALUE <операция_сравнения> <значение> | VALUE [NOT] BETWEEN <значение1> AND <значение2> | VALUE [NOT] LIKE 'шаблон' [ESCAPE 'символ_пропуска' ] | VALUE [NOT] CONTAINING <значение> | VALUE [NOT] STARTING [WITH] <значение> | VALUE [NOT] IN (<значение1> [ , <значение2> …]) | VALUE IS [NOT] NULL | VALUE IS [NOT] DISTINCT FROM <значение> | VALUE SIMILAR TO 'регулярное_выражение' [ESCAPE 'символ_пропуска']}; <значение> ::= { столбец | константа | <выражение> | функция}. Ключевое слово VALUE используется как обозначение значений, которые будут помещаться в столбец таблицы, имеющий тип соответствующего домена. Например, для определения домена с именем Telephone, описывающего номер телефона абонента (по умолчанию '999999', не может быть значения '100000') и имеющего тип VARCHAR(10), следует применить запрос: CREATE DOMAIN Telephone AS VARCHAR(10) DEFAULT '999999' CHECK (VALUE != '100000'); После определения домена его имя используется для определения типа данных соответствующих столбцов таблиц. 1. Примечание. Если в таблице присутствует 1 столбец и он имеет значение по умолчанию, то его не удастся использовать, так как требуется при вставке в таблицу указать явно хотя бы один столбец. В учебной БД Firebird используются 4 домена, запросы определения которых выглядят следующим образом: CREATE DOMAIN Money AS NUMERIC(15,2); CREATE DOMAIN PKField AS INTEGER; CREATE DOMAIN TMonth AS SMALLINT CHECK (VALUE BETWEEN 1 AND 12); CREATE DOMAIN TYear AS SMALLINT CHECK (VALUE BETWEEN 1990 AND 2100); Просмотреть список доменов, определенных в БД, и структуру каждого из них можно в IBExpert с помощью инспектора объектов. Определение существующего домена можно изменить с помощью запроса ALTER DOMAIN. Этот запрос позволяет: 4 удалить существующее и определить новое значение по умолчанию (заменяя при этом старое значение, если оно было указано);  удалить существующее и ввести новое ограничение целостности. Синтаксис запроса ALTER DOMAIN имеет формат: ALTER DOMAIN имя_домена {[SET DEFAULT { литерал | NULL }] | [DROP DEFAULT] | [ADD [CONSTRAINT] CHECK (<ограничение_домена>)] | [DROP CONSTRAINT] | [[NOT] NULL]};,  где SET DEFAULT – указывает для существующего домена значение по умолчанию; DROP DEFAULT – удаляет для существующего домена значение по умолчанию; ADD [CONSTRAINT] CHECK – добавляет ограничение для существующего домена; DROP CONSTRAINT – удаляет CHECK ограничение из определения домена; [NOT] NULL – изменяет признак допустимости значений NULL для домена. Созданный домен Telephone можно изменить, удалив ограничение, запросом: ALTER DOMAIN Telephone DROP CONSTRAINT; или установить другое значение по умолчанию запросом: ALTER DOMAIN Telephone SET DEFAULT '111111'; Существующий домен можно удалить с помощью запроса DROP DOMAIN, имеющего синтаксис: DROP DOMAIN имя_домена; 2. Примечание. Домен не будет удален, если на него имеются какиелибо ссылки, т. е. существуют таблицы со столбцами, определенными на этом домене. Например, если попытаться на учебной БД удалить домен Money: DROP DOMAIN Money;, то будет выдано сообщение об ошибке: «Domain MONEY is used in table NACHISLSUMMA (local name NACHISLSUM) and cannot be dropped» [домен MONEY используется в таблице NACHISLSUMMA (локальное имя NACHISLSUM) и не может быть удален]. 4.2. СОЗДАНИЕ, ИЗМЕНЕНИЕ И УДАЛЕНИЕ БАЗОВЫХ ТАБЛИЦ БД 4.2.1. Создание таблицы В реляционной БД наиболее важным элементом ее структуры является таблица. В таблицах содержатся все обрабатываемые данные исходной предметной области. Перед тем как перейти к созданию таблиц, необходимо выполнить проектирование БД и нормализацию таблиц. Кроме этого, приступая к созданию таблицы, необходимо ответить на следующие вопросы. 5 Как будет называться таблица?  Как будут называться столбцы таблицы?  Какие типы данных будут закреплены за каждым столбцом?  Какие столбцы таблицы требуют обязательного ввода?  Из каких столбцов будет состоять первичный ключ и т. д.?  Какие из ограничений целостности необходимо реализовать непосредственно при определении таблицы, т. е. декларативно, а какие процедурно, т. е. с помощью триггеров или хранимых процедур и т. д.? Базовая таблица обычно может быть создана с помощью графической утилиты для работы с БД конкретной СУБД, а также выполнением запроса в SQL-редакторе или в скрипте. Например, в Firebird базовая таблица может быть создана в IBExpert: - с помощью конструктора, вызываемого по Ctrl+N на закладке Таблицы инспектора объектов БД; - из пункта Новая таблица меню База данных; - выполнением запроса в SQL-редакторе или в редакторе скриптов, вызываемых из меню Инструменты. Для создания базовой таблицы, ее столбцов и ограничений, налагаемых на каждый столбец, используется запрос CREATE TABLE. В СУБД Firebird запрос имеет формат: CREATE TABLE базовая_таблица (<определение_столбца1> [, <определение_столбца2> | <ограничение_таблицы>] … ); Элементы в круглых скобках после имени таблицы могут представлять собой как определения столбцов, так и ограничения базовой таблицы. Конструкция <определение_столбца> имеет вид: <определение_столбца> :: = столбец { <тип_данных> | имя_домена | {COMPUTED [BY] | GENERATED ALWAYS AS} (<выражение>) } [ DEFAULT { литерал | NULL } ] [ NOT NULL ] [ GENERATED BY DEFAULT AS IDENTITY [START WITH начальное_значение] ] [<ограничение_столбца>] . Все столбцы таблицы должны иметь уникальные имена. Тип данных столбца может задаваться непосредственно указанием типа или указанием имени домена. С точки зрения теории БД использование доменов при определении типа столбца является необходимым. При этом домены должны создаваться до создания таблиц. Конструкция COMPUTED [BY] (<выражение>) определяет, что значение столбца вычисляется во время выполнения запроса в соответствии с указанным выражением. При этом выражение должно возвращать одно значение, а столбцы таблицы, указанные в выражении, должны существовать до их использования. Для определения вычисляемого столбца также может использоваться эквивалентная конструкция GENERATED ALWAYS AS (<выражение>).  6 Например, если требуется поместить в столбец Summa значение суммы столбцов First и Second, описание типа данных столбца Summa должно выглядеть так: Summa COMPUTED BY (First + Second) или так: Summa GENERATED ALWAYS AS (First + Second) . В результате тип данных столбца Summa будет автоматически приведен к типу данных столбцов First и Second по правилу преобразования типов (см. табл. 2.6). При конкатенации строк, содержащих имя (Name) и отчество (Second_Name), для получения полного имени (Full_Name) определение столбца Full_Name, содержащего запятую в качестве разделителя, может быть таким: Full_Name COMPUTED BY (Name || ',' || Second_Name). Столбец Full_Name будет иметь строковый тип данных с длиной, равной сумме длин строк Second_Name и Name. Очевидно, что определенные таким образом столбцы Summa и Full_Name будут доступны только для чтения (read-only column), т. е. в эти столбцы нельзя добавлять значения, а следовательно, они не должны упоминаться в списке столбцов запросов INSERT и UPDATE. Опция NOT NULL устанавливает ограничение на непустое значение столбца (условие обязательности данных). Так, например, столбец первичного ключа не может содержать NULL, следовательно, при его описании должно быть использовано ограничение NOT NULL. Опция DEFAULT определяет значение столбца по умолчанию, т. е. то значение, которое будет вставляться в таблицу при добавлении новой строки. Если таблица содержит 1 столбец, то у него не должно быть значения по умолчанию. Опция GENERATED BY DEFAULT AS IDENTITY определяет столбецидентификатор в таблице [40, 42]. Столбец-идентификатор – это столбец, связанный с внутренним генератором, значение такой столбец получает автоматически при выполнении запроса INSERT к таблице. В качестве типа данных для такого столбца может быть задан SMALLINT, INTEGER, BIGINT, NUMERIC или DECIMAL с нулевой точностью. Столбец-идентификатор не может быть вычисляемым (нельзя использовать конструкцию COMPUTED), не может иметь значения по умолчанию (нельзя использовать опцию DEFAULT) и не допускает значения NULL (всегда NOT NULL). При создании столбца-идентификатора следует учесть, что нельзя потом изменить столбец-идентификатор на обычный и наоборот. Также необходимо помнить, что столбец-идентификатор не обеспечивает уникальность автоматически, так как в столбец может быть добавлено и не уникальное (явно указанное) значение с помощью запроса модификации. В качестве столбца с уникальным значением необходимо использовать столбец, являющийся первичным ключом. Например, необходимо создать таблицу Days, состоящую из шести столбцов: столбца-идентификатора Number типа INTEGER, Dat типа DATE, Event типа VARCHAR(20), Implemented типа BOOLEAN, Usr типа CHAR(10) 7 и Tel, определенного на созданном домене Telephone. При этом столбец Dat по умолчанию должен содержать дату 1 сентября 2013 г., столбец Event по умолчанию должен содержать NULL-маркеры, столбец Implemented не должен содержать NULL, столбец Usr по умолчанию должен содержать имя пользователя, подключенного к БД, а столбец Tel должен содержать значение по умолчанию из домена Telephone. Запрос для создания таблицы Days примет вид: CREATE TABLE Days ( Number INTEGER GENERATED BY DEFAULT AS IDENTITY, Dat DATE DEFAULT '09/01/2013', Event VARCHAR(20) DEFAULT NULL, Implemented BOOLEAN NOT NULL, Usr CHAR(10) DEFAULT CURRENT_USER, Tel Telephone); Если определение столбца основано на домене, оно может включать новое значение по умолчанию и/или дополнительные ограничения, которые перекрывают значения, заданные при определении домена. Например, можно добавить ограничение NOT NULL для столбца, если домен его еще не содержит. Однако домен, который был определен как NOT NULL, не может быть переопределен на уровне столбца как допускающий NULL значение. В СУБД Oracle синтаксис запроса CREATE TABLE имеет формат: CREATE TABLE базовая_таблица { (<определение_столбца1> [, <определение_столбца2> | <ограничение_таблицы>] … ) | AS SELECT { * | столбец1 [, столбец2 …] } FROM родительская_таблица}; Таким образом, Oracle предоставляет дополнительный способ создания таблицы – «позаимствовать» определение, включая имена столбцов и типы данных, у существующей таблицы. При выполнении запроса CREATE TABLE AS с подзапросом SELECT создается таблица, структура которой идентична «родительской» таблице. После этого в новую таблицу копируется содержимое выбранных столбцов старой. Синтаксис определения столбца в Oracle практически совпадает с синтаксисом в Firebird, за исключением того, что не могут использоваться: - конструкция COMPUTED BY; - опция GENERATED BY DEFAULT AS IDENTITY. Описывая создание различных объектов, в частности таблиц, в БД Oracle, следует отметить важную особенность. После установки Oracle доступна предопределенная администраторская учетная запись SYS. Данную учетную запись следует использовать очень осторожно (и редко), не применяя ее для создания таблиц и других объектов [45]. Иначе, например, впоследствии невозможно будет создать триггеры для объектов, владельцем которых является SYS. При начале работы с БД нужно создать обычную учетную запись, наделить ее необходимыми привилегиями (см. далее гл. 7) и создавать объекты БД уже от имени обычного пользователя. 8 В MS SQL Server синтаксис запроса CREATE TABLE расширен, так как MS SQL Server позволяет создавать файловые таблицы (FileTable) и секционированные таблицы [23, 37]. Файловые таблицы позволяют хранить файлы в виде особых таблиц SQL Server. При этом доступ к данным таких файлов возможен как из приложений, работающих с обычной файловой системой вне контекста какой-либо транзакции, так и с использованием всех средств языка TransactSQL. Все файловые таблицы имеют одинаковую заранее установленную структуру, поэтому при создании такой таблицы не нужно описывать ее столбцы, все они создаются автоматически. Таблица содержит столбцы: Name, задающий имя файла или каталога, file_type, указывающий тип файла, а так же ряд столбцов, описывающих такие характеристики файла, как дата создания, размер, является ли объект файлом или каталогом, можно ли его использовать только для чтения и др. Каждая строка описывает файл на внешнем носителе или каталог. Секционированные таблицы позволяют на основании некоторого критерия размещать отдельные строки таблицы в различных файловых группах и дают возможность повысить производительность системы, а также ее отказоустойчивость. Более подробно с созданием файловых и секционированных таблиц в MS SQL Server можно ознакомиться в [23]. В рамках учебника рассмотрим упрощенный синтаксис запроса CREATE TABLE в MS SQL Server: CREATE TABLE базовая_таблица (<определение_столбца1> [, <определение_столбца2> | <ограничение_таблицы>] … ) [ WITH (<параметр таблицы> [, <параметр таблицы>] ...) ];, где <определение_столбца> :: = столбец { <тип_данных> | AS ( <выражение> ) } {[ COLLATE <порядок_сортировки> ] [ SPARSE ] [ NULL | NOT NULL] { [DEFAULT <выражение>] | [ IDENTITY [ (<начальное_значение>,<приращение>) ]]} [ ROWGUIDCOL ] | [ PERSISTED [ NOT NULL ] ] } [<ограничение_столбца>]. Секция WITH позволяет указать различные параметры таблицы. Конструкция AS ( <выражение> ) определяет вычисляемый столбец. Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлена опция PERSISTED. Опция PERSISTED указывает, что MS SQL Server будет физически хранить вычисляемые значения в таблице и обновлять их при изменении любого столбца, от которого зависит вычисляемый столбец. Опция PERSISTED 9 позволяет создать индекс по вычисляемому столбцу. Также для вычисляемых столбцов с опцией PERSISTED может быть указано ограничение NOT NULL. Опция COLLATE позволяет для строкового столбца задать порядок сортировки, отличный от порядка, установленного для всей БД. Опция SPARSE указывает, что столбец является разреженным. Такой тип столбцов имеет смысл использовать для экономии внешней памяти, если в строках таблицы присутствует большое количество значений NULL для соответствующего столбца. Опция может быть указана для любых типов данных за исключением TEXT, NTEXT, IMAGE, TIMESTAMP, GEOMETRY, GEOGRAPHY, а также пользовательских типов данных. Разреженный столбец не может быть объявлен как NOT NULL и FILESTREAM [37]. Конструкция IDENTITY позволяет создать автоинкрементный столбец. По умолчанию для первой добавляемой строки таблицы этому столбцу будет установлено значение 1, все последующие присваиваемые значения будут увеличиваться на единицу. Характеристики по умолчанию для столбцов IDENTITY можно изменить, задав начальное_значение и приращение. К автоинкрементным столбцам нельзя определять значения по умолчанию DEFAULT. Опция ROWGUIDCOL указывает, что новый столбец является столбцом идентификаторов GUID строки. Только один столбец типа UNIQUEIDENTIFIER в таблице может быть назначен в качестве столбца ROWGUIDCOL. Для поддержания целостности БД на таблицы накладываются ограничения (CONSTRAINT). Под ограничением понимается условие, которое должно выполняться при хранении, обновлении и добавлении данных в таблицу БД. По области применения различают ограничения, накладываемые:  на определенный столбец;  на всю таблицу. Принципиально они друг от друга не отличаются. Однако ограничения на значения столбцов проверяются для отдельных столбцов (такие ограничения являются частью определения отдельного столбца), а ограничения на таблицы проверяются для таблицы в целом (такие ограничения записываются в таблице только раз). 4.2.2. Определение ограничений столбца При ограничении столбца соответствующее ограничение объявляется индивидуально для каждого столбца непосредственно после определения имени и типа столбца. При этом используется секция <ограничение_столбца>. В СУБД Firebird данная конструкция имеет формат: <ограничение_столбца>::= [CONSTRAINT имя_ограничения] { UNIQUE | PRIMARY KEY | CHECK (<условие_проверки>) 10 | REFERENCES родительская_таблица [(столбец)] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]}. Рекомендуется ограничениям давать имена, так как при необходимости изменения определения таблицы удалить ограничение можно только по его имени. При создании таблицы имя ограничения задается произвольно, но должно быть уникальным для данной таблицы и, желательно, отражать смысл ограничения. Если «CONSTRAINT имя_ограничения» отсутствует, то СУБД присваивает ограничению свое системное имя. Имя ограничения обычно можно посмотреть с помощью утилиты для работы с БД конкретной СУБД (например, для БД Firebird – в IBExpert на закладке «Ограничения») для каждого столбца конкретной таблицы. Фактически существует три вида ограничений: - ограничения уникальности, которые включают в себя ограничения уникального (UNIQUE) и первичного ключа (PRIMARY KEY); - проверяемые ограничения (CHECK); - ограничения целостности (REFERENCES, FOREIGN KEY). Опция UNIQUE определяет наличие у столбца уникальных значений, т. е. при изменении данных в таблице (добавление или обновление строк) автоматически будет осуществляться проверка отсутствия подобного значения. Опция PRIMARY KEY определяет столбец в качестве первичного ключа. Столбец таблицы, определенный как первичный ключ, должен обязательно иметь ограничение NOT NULL. Основное различие между ограничениями PRIMARY и UNIQUE состоит именно в том, что UNIQUE допускает NULLзначения. Рассмотрим запрос CREATE TABLE c ограничением определенного столбца. Например, чтобы создать таблицу Abonent, определив столбец AccountCD в качестве первичного ключа, необходимо использовать запрос: CREATE TABLE Abonent (AccountCD VARCHAR(6) NOT NULL CONSTRAINT Xpka PRIMARY KEY, StreetCD INTEGER, HouseNo SMALLINT, FlatNo SMALLINT, Fio VARCHAR(20), Phone VARCHAR(15)); Таким образом, при создании таблицы Abonent определяется ограничение с именем Xpka, указывающее в качестве первичного ключа таблицы столбец AccountCD. В качестве примеров столбцов, которые должны иметь ограничение UNIQUE, могут быть столбцы с номерами телефонов или социальные номера индивидуальных лицевых счетов (если они не являются первичными ключами) и т.д. Например, таблица Abonent учебной БД содержит столбец с уникальным номером телефона. Чтобы гарантировать, что данные значения уникальны (не дублируются), создавая данную таблицу, можно включить в 11 определение столбца Phone ограничение UNIQUE. После этого СУБД проследит за тем, чтобы любое значение, вводимое любым пользователем в столбец Phone, было единственным в своем роде. Ниже приведен пример задания ограничения UNIQUE в виде ограничения на столбец. CREATE TABLE ABONENT ( ACCOUNTCD VARCHAR(6) NOT NULL PRIMARY KEY, STREETCD INTEGER, HOUSENO SMALLINT, FLATNO SMALLINT, Fio VARCHAR(20), PHONE VARCHAR(15) UNIQUE); . Для каждого столбца можно назначить условие проверки указанием в ограничении столбца опции CHECK (<условие_проверки>). Каждый раз, когда в такой столбец будут добавляться новые данные или обновляться существующие, автоматически будет происходить их проверка в соответствии с <условием проверки>, которое имеет формат: <условие_проверки>::= [NOT] <условие_проверки1> [[AND|OR][NOT] <условие_проверки2>]…, где <условие_проверки>::= {<значение> <операция_сравнения> { <значение1> | (<скалярный_подзапрос>) | {ANY| ALL} (<подзапрос_столбца>)} | <значение> [NOT] BETWEEN <значение1> AND <значение2> | <значение> [NOT] LIKE 'шаблон' [ESCAPE 'символ пропуска'] | <значение> [NOT] CONTAINING <значение1> | <значение> [NOT] STARTING [WITH] <значение1> | <значение> [NOT] IN (<значение1> [ , <значение2> …] | <подзапрос_столбца>) | <значение> IS [NOT] NULL | <значение> IS [NOT] DISTINCT FROM <значение1> | EXISTS (<табличный_подзапрос>) | SINGULAR (<табличный_подзапрос>)} | <значение> [NOT] SIMILAR TO 'регулярное_выражение' [ESCAPE 'символ_пропуска'] , в котором <значение> ::= { столбец | константа | <выражение> | функция}; <операция_сравнения> ::= { = | < | > | {<= | !< } | { >= | !> } | { <> | != }}; <табличный_подзапрос>::= запрос SELECT, возвращающий набор строк и столбцов; <подзапрос_столбца>::= запрос SELECT, возвращающий значения одного столбца, но, возможно, в нескольких строках; <скалярный_подзапрос>::= запрос SELECT, возвращающий значение одного столбца в одной строке. По существу, <условие_проверки> – это не что иное, как условие поиска секции WHERE при использовании вложенных запросов. Если столбец таблицы определен на домене, имеющем ограничение CHECK, то это ограничение не может быть переопределено в определении 12 столбца, хотя столбец может расширить использование ограничения CHECK домена, добавив свои собственные условия. Например, необходимо создать таблицу NachislSumma, определив столбец NachislFactCD как первичный ключ на домене PKField. При этом начисленная сумма (столбец NachislSum на домене Money) не должна быть меньше 5000, значение столбца NachislYear, определяемого на домене TYear с ограничением (VALUE BETWEEN 1990 AND 2100), должно отличаться от 2005. Запрос на создание этой таблицы: CREATE TABLE NachislSumma (NachislFactCD PKField NOT NULL PRIMARY KEY, AccountCD VARCHAR(30) NOT NULL, ServiceCD PKField NOT NULL, NachislSum Money CHECK (NachislSum >= 5000), NachislMonth TMonth, NachislYear TYear CHECK (NachislYear IS DISTINCT FROM 2005)); Если значения, помещаемые в таблицу NachislSumma, не будут удовлетворять указанным условиям проверки, то возникнет ошибка с SQLCODE=-297. Секция REFERENCES, указанная в качестве ограничения столбца, задает, что данный столбец таблицы ссылается на родительскую таблицу и является внешним ключом. Внешний ключ может ссылаться на уникальный или первичный ключ родительской таблицы. Если после имени таблицы, на которую ссылается данный внешний ключ, не указано имя столбца, то подразумевается, что данный столбец ссылается на первичный ключ. 3. Примечание. Ключ может ссылаться на первичный ключ той же самой таблицы, т. е. может быть реализовано рекурсивное отношение. Опции ON DELETE и ON UPDATE используются вместе с REFERENCES при определении внешнего ключа и предназначены для описания типа изменения внешнего ключа при изменении соответствующего ему значения первичного ключа, т. е. для столбца внешнего ключа таблицы-потомка задаются действия, автоматически выполняемые при удалении или обновлении столбца первичного ключа в таблице-родителе, на который ссылается данный внешний ключ. В стандартном SQL правил удаления, как и правил обновления, всего четыре: RESTRICT, CASCADE, SET NULL и SET DEFAULT. Однако правила SET NULL и SET DEFAULT некоторыми СУБД вообще не поддерживаются, а правило RESTRICT не нуждается в явном указании, так как в большинстве случаев принимается по умолчанию. Для указания действий, которые должны выполняться над столбцом внешнего ключа при удалении и обновлении данных столбца первичного ключа, в Firebird используются следующие параметры:  NO ACTION (используется по умолчанию) означает, что удаление или обновление первичного ключа родительской таблицы не изменяет 13 ссылающийся внешний ключ, вследствие чего попытка операции над родительской таблицей может закончиться неудачей;  CASCADE для ON DELETE удаляет строки, содержащие значение ссылающегося внешнего ключа, а для ON UPDATE обновляет ссылающийся внешний ключ новым значением первичного ключа;  SET DEFAULT устанавливает значение ссылающегося внешнего ключа в заданное для него значение по умолчанию. Чтобы выполнялось это ограничение, для всех столбцов внешних ключей целевой таблицы должно быть определено значение по умолчанию. Если столбец допускает неопределенные значения и не задано явно множество значений по умолчанию, NULL становится неявным значением по умолчанию для данного столбца. Все задаваемые опцией ON UPDATE SET DEFAULT или ON DELETE SET DEFAULT значения, отличные от NULL, должны иметь соответствующие значения в основной таблице, чтобы сохранить целостность ограничения внешнего ключа;  SET NULL устанавливает значение ссылающегося внешнего ключа в NULL. Опции ON DELETE и ON UPDATE могут использоваться одновременно, т. е. для столбца в одном ограничении могут быть указаны действия, которые необходимо выполнить при удалении, а также действия, которые необходимо выполнить при обновлении. Например, необходимо создать таблицу Req, определив столбец AccountCD в качестве внешнего ключа, ссылающегося на первичный ключ таблицы Abonent. Необходимо также, чтобы при удалении столбца первичного ключа в таблице Abonent удалялись строки с соответствующим значением внешнего ключа в таблице Req. При обновлении первичного ключа в таблице Abonent должно происходить обновление соответствующего внешнего ключа в таблице Req. Следующий запрос создает требуемую таблицу: CREATE TABLE Req (RequestCD INTEGER NOT NULL PRIMARY KEY, AccountCD VARCHAR(6) REFERENCES Abonent(AccountCD) ON DELETE CASCADE ON UPDATE CASCADE, ExecutorCD INTEGER, FailureCD INTEGER, IncomingDate DATE, ExecutionDate DATE, Executed BOOLEAN); В Oracle синтаксис определения ограничений столбца имеет некоторые отличия от синтаксиса в Firebird в части видов действий, которые должны выполняться над столбцом внешнего ключа при удалении данных столбца первичного ключа: <ограничение_столбца>::= [CONSTRAINT имя_ограничения] { UNIQUE | PRIMARY KEY | CHECK (<условие_проверки>) | REFERENCES родительская_таблица [(столбец)] [ON DELETE { NO ACTION | CASCADE | SET NULL}] }. 14 Таким образом, задание правил обновления данных не поддерживается в Oracle. Для удаления данных существует 3 правила:  CASCADE удаляет (но не обновляет) родительскую строку и вместе с ней все дочерние;  SET NULL устанавливает пустые значения для дочерних строк в случае удаления (но не обновления) родительской;  NO ACTION (по умолчанию) запрещает выполнение операций удаления родительской строки, если у нее есть дочерние. Правило SET DEFAULT в Oracle не поддерживается. MS SQL Server поддерживает следующий синтаксис определения ограничений столбца: <ограничение_столбца>::= [CONSTRAINT имя_ограничения] { UNIQUE [ CLUSTERED | NONCLUSTERED ] [ WITH (<параметр индекса> [, <параметр индекса>]...) ] | PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ WITH (<параметр индекса> [, <параметр индекса>]...) ] | CHECK (<условие_проверки>) | [ FOREIGN KEY ] REFERENCES родительская_таблица [(столбец)] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]}. Особенностью MS SQL Server в плане задания ограничений является то, что можно указать, какой индекс будет использоваться для поддержки ключа. Опция CLUSTERED задает кластерный (кластеризованный) индекс, NONCLUSTERED – некластерный (некластеризованный). По умолчанию для первичного ключа создается кластерный индекс, для уникального ключа – некластерный. Таблица может иметь только один кластерный индекс. Опция WITH позволяет задать ряд дополнительных параметров индекса. Синтаксис определения внешнего ключа без отличительных особенностей. При этом в определении внешнего ключа для вычисляемого столбца сокращены варианты реагирования на изменение и удаление соответствующей строки родительской таблицы: [ FOREIGN KEY ] REFERENCES родительская_таблица [(столбец)] [ ON DELETE { NO ACTION | CASCADE } ] [ ON UPDATE NO ACTION ]. В случае удаления можно указать лишь NO ACTION (по умолчанию) или CASCADE, а при изменении значения ключевого реквизита родительской таблицы – только NO ACTION, которое и так действует по умолчанию. Такое поведение системы понятно. Поскольку нельзя напрямую изменять значение вычисляемого столбца, то при удалении строки родительской таблицы можно лишь удалить все строки подчиненной таблицы или не выполнять 15 никаких действий. При изменении значения ключа родительской таблицы в принципе невозможны никакие действия со значением вычисляемого столбца дочерней таблицы. Рассмотренные здесь примеры запросов на создание таблицы содержали ограничения только на уровне столбца. Как можно отключить созданное ограничение, рассмотрено в п. 4.2.5 при изучении возможностей по изменению определений таблиц. Далее рассмотрим возможности наложения ограничений на уровне таблицы. 4.2.3. Определение ограничений на таблицу Определение ограничения на таблицу осуществляется после описания всех столбцов, при этом используется конструкция <ограничение_таблицы>. Ограничения уровня таблиц объявляются независимо от определений столбцов и могут применяться к одному или нескольким столбцам. Ограничение уровня таблицы, как правило, необходимо в том случае, если нужно создать ограничение более чем к одному столбцу. При определении ограничений, накладываемых на всю таблицу, используется синтаксическая конструкция <ограничение_таблицы>. В СУБД Firebird данная конструкция имеет формат: <ограничение_таблицы>::= [CONSTRAINT имя_ограничения] {{ PRIMARY KEY | UNIQUE} (<список_столбцов>) | FOREIGN KEY (<список_столбцов>) REFERENCES родительская_таблица [(столбец1 [, столбец2 …])] [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}] | CHECK (<условие_проверки>) } [USING [ASC[ENDING] | DESC[ENDING]] INDEX имя_индекса]. Существует 3 вида ограничений базовой таблицы: 1) ограничение уникальности (уникального, первичного ключа); 2) ссылочное ограничение (ограничение внешнего ключа); 3) ограничение «проверочного условия». При задании ограничения таблицы можно определить первичный ключ (как простой, так и составной). Если первичный ключ не является составным, то его можно определить при задании ограничения столбца (см. разд. 4.2.2), но составной первичный ключ можно определить только с помощью ограничения таблицы. Для этого столбцы таблицы, являющиеся составным первичным ключом, перечисляются в круглых скобках через запятую в опции PRIMARY KEY. Аналогично при задании ограничения таблицы можно определить как простой, так и составной внешний ключ, причем если простой ключ уже был определен в ограничении столбца, то определять его снова не требуется. Составной внешний ключ можно определить только с помощью ограничения таблицы, и для этого используется опция FOREIGN KEY со списком 16 столбцов и опцией REFERENCES c именами содержащих их таблиц, на которые ссылается данный внешний ключ. Использование параметров NO ACTION, CASCADE, SET DEFAULT и SET NULL в опциях ON DELETE и ON UPDATE аналогично использованию их при определении ограничения столбца (см. разд. 4.2.2). Таким образом, если определяется простой ключ (первичный или внешний), то его можно задать как в ограничении столбца, так и в ограничении таблицы – результат будет одинаков. Однако, если ключ составной, то его можно определить только в ограничении на таблицу. Например, чтобы при создании таблицы NachislSumma определить составной первичный ключ, состоящий из столбцов NachislFactCD и NachislYear, и внешние ключи AccountCD и ServiceCD, необходимо применить запрос: CREATE TABLE NachislSumma (NachislFactCD INTEGER NOT NULL, NachislSum NUMERIC(15,2), NachislYear SMALLINT NOT NULL, NachislMonth SMALLINT, AccountCD VARCHAR(6) NOT NULL, ServiceCD INTEGER NOT NULL, PRIMARY KEY (NachislFactCD, NachislYear), FOREIGN KEY (AccountCD) REFERENCES Abonent(AccountCD), FOREIGN KEY (ServiceCD) REFERENCES Services(ServiceCD)); При этом в качестве действия на изменение первичных ключей таблиц Abonent и Services будет определено действие NO ACTION, используемое по умолчанию. При использовании ограничения UNIQUE накладывается условие уникальности на значения заданных столбцов таблицы. В п. 4.2.2 был приведен пример задания ограничения UNIQUE для столбца Phone таблицы Abonent учебной БД. Рассмотрим теперь задание ограничения UNIQUE в виде ограничения на таблицу: CREATE TABLE ABONENT ( ACCOUNTCD VARCHAR(6) NOT NULL PRIMARY KEY, STREETCD PKFIELD REFERENCES STREET ON DELETE SET NULL ON UPDATE CASCADE, HOUSENO SMALLINT, FLATNO SMALLINT, Fio VARCHAR(20), PHONE VARCHAR(15), CONSTRAINT Phone UNIQUE(Phone)); Одно из основных отличий ограничения на столбец от ограничения на таблицу заключается в том, что в первом случае ограничение UNIQUIE накладывается только на один столбец. Если же используется задание ограничений на таблицу, ограничение UNIQUIE можно будет задать для нескольких столбцов, перечисленных в скобках через запятую. В столбцах с уникальными ограничениями можно использовать NULL значения. Следует учесть, что если в столбцы, на значения которых наложено 17 условие уникальности, пытаться ввести только NULL значения, то они не будут считаться одинаковыми и команда будет выполнена успешно. Создадим, например, таблицу Phone_Sprav с 4 столбцами: столбец Number типа INTEGER, столбец Fio типа VARCHAR(25), столбец Home_Phone типа VARCHAR(6) и столбец Mobil_Phone типа VARCHAR(11). При этом столбец Number определим в качестве первичного ключа, а в качестве ограничения на таблицу определим уникальность столбцов Home_Phone и Mobil_Phone. Запрос CREATE TABLE для создания таблицы Phone_Sprav примет вид: CREATE TABLE Phone_Sprav (Number INTEGER PRIMARY KEY, Fio VARCHAR(25), Home_Phone VARCHAR(6), Mobil_Phone VARCHAR(11), UNIQUE (Home_Phone, Mobil_Phone)); Если попытаться добавить в таблицу строки (позже будет рассмотрено, как это можно сделать), в которых значения столбцов Number и Fio различны, а столбцов Home_Phone и Mobil_Phone содержат NULL, то вставка этих данных пройдет успешно, так как считается, что каждое (NULL, NULL) в уникальных столбцах (Home_Phone, Mobil_Phone) отличается от любого другого (NULL, NULL). Однако попытка вставить строки с одинаковыми значениями в одном из уникальных столбцов (Home_Phone или Mobil_Phone) и с NULL значениями в другом не удастся. Таким образом, если хотя бы для одного столбца из списка, на который наложено условие уникальности, повторяется значение, отличное от NULL, то запрос не будет выполнен. Ограничение CHECK (<условие_проверки>) может использоваться не только при определении отдельного столбца, но и в качестве табличного ограничения. Это полезно в тех случаях, когда условие необходимо задать на значениях нескольких столбцов. Синтаксис <условия_проверки> фактически повторяет рассмотренный выше для определения ограничения столбца, но могут использоваться имена нескольких различных столбцов таблицы, на которую накладывается ограничение. Предположим, что поступившая ремонтная заявка должна выполняться не позднее чем через неделю после поступления (дата выполнения должна быть в диапазоне от дня поступления до даты на 7 дней позднее). Запрос на создание таблицы Request с учетом этого ограничения можно представить так: CREATE TABLE Request (RequestCD INTEGER NOT NULL PRIMARY KEY, AccountCD VARCHAR(6) REFERENCES Abonent(AccountCD) ON DELETE CASCADE ON UPDATE CASCADE, ExecutorCD INTEGER, FailureCD INTEGER, IncomingDate DATE, ExecutionDate DATE, Executed BOOLEAN, CONSTRAINT ExecDat CHECK (ExecutionDate BETWEEN IncomingDate AND IncomingDate+7)); Следует обратить внимание на то, что проверяются значения разных столбцов одной и той же строки – нельзя проверить значения более чем в одной строке. Также нельзя использовать ограничение CHECK, например, 18 для того, чтобы указать зависимость даты выполнения одной ремонтной заявки от даты поступления остальных. Рассмотренные здесь примеры запросов на создание таблицы содержали ограничения на уровне столбца и на уровне таблицы. Секция USING формата [USING [ASC[ENDING] | DESC[ENDING]] INDEX имя_индекса], используемая только в СУБД Firebird, будет рассмотрена при изучении индексов (см. п. 4.3). В MS SQL Server имеются аналогичные особенности в плане задания ограничений уникальности для таблицы: { PRIMARY KEY | UNIQUE} [ CLUSTERED | NONCLUSTERED ] (<столбец> [ ASC | DESC ] [, <столбец> [ ASC | DESC ] ]...) [ WITH (<параметр индекса> [, <параметр индекса>]...) ]. Для столбцов первичного и уникального ключа (только на уровне таблицы, но не на уровне столбца) можно указать упорядоченность в создаваемом для этого ключа индексе. Опция ASC означает упорядочение по возрастанию значений, опция DESC – по убыванию. Причем в рамках одного составного первичного или уникального ключа отдельные столбцы могут упорядочиваться по возрастанию, другие – по убыванию, т. е. допустима смешанная упорядоченность. По умолчанию принимается упорядоченность столбцов по возрастанию значений. В Oracle отличия в синтаксисе определения внешнего ключа для таблицы аналогичны отличиям при определении ограничения столбца. 4.2.4. Удаление таблицы БД Для удаления существующей таблицы используется запрос DROP TABLE: DROP TABLE базовая_таблица; При этом из БД удаляются данные, метаданные и индексы таблицы, а также все триггеры, которые обращаются к таблице. Следует отметить, что обычно нельзя удалить таблицу в случаях:  если на ее столбцы ссылаются внешние ключи других таблиц;  если она используется другими объектами БД (например, представлением);  если она определена в текущей транзакции, на момент удаления еще не завершенной. Например, в Firebird при попытке удалить используемую таблицу выдается сообщение об ошибке c SQLCODE=-607 и сообщение, что удаляемый объект еще находится в использовании. Однако некоторые СУБД допускают удаление таблицы и в случае наличия ссылок на нее. Например, в Oracle синтаксис удаления таблицы имеет следующий формат: DROP TABLE базовая_таблица [CASCADE CONSTRAINTS]; Опция CASCADE CONSTRAINTS удаляет все ограничения целостности, которые ссылаются на первичные и уникальные ключи удаляемой таблицы. 19 В MS SQL Server запрос DROP TABLE позволяет удалить сразу несколько таблиц, отделяя их имена запятыми: DROP TABLE базовая_таблица [,базовая_таблица]...; 4.2.5. Изменение определения таблицы Созданную запросом CREATE TABLE базовую таблицу можно изменить запросом ALTER TABLE. В различных СУБД поддерживаемые изменения определения таблицы несколько отличаются, рассмотрим сначала изменение определения таблицы в Firebird, а затем остановимся на отличиях СУБД Oracle и MS SQL Server. Firebird поддерживает следующие изменения:  добавление новых столбцов;  задание нового ограничения целостности для базовой таблицы;  определение нового имени для существующего столбца;  изменение типа данных для существующего столбца;  изменения порядкового номера столбца в таблице;  определение для существующего столбца нового значения по умолчанию (заменяющего предыдущее значение, если оно было);  удаление для столбца существующего значения по умолчанию;  удаление существующего столбца;  удаление существующего ограничения целостности для базовой таблицы. Запрос ALTER TABLE имеет формат: ALTER TABLE базовая_таблица <действие1> [, <действие2> …];, где базовая_таблица – это имя существующей базовой таблицы БД, определение которой требуется изменить; <действие> задает действия, которые будут производиться с указанной таблицей, и определяется таким образом: <действие>::= {ADD <определение_столбца> | ADD <ограничение_таблицы> | ALTER [COLUMN] столбец <изменение> | DROP столбец | DROP CONSTRAINT имя_ограничения} | ADD <ограничение_столбца>, где <изменение>::= {TO новое_имя_столбца | TYPE {<тип данных> | имя_домена} | POSITION номер_позиции | SET DEFAULT <значение> | DROP DEFAULT | COMPUTED BY (выражение) | SET NOT NULL | DROP NOT NULL }. При использовании конструкции ADD <определение_столбца> 20 в таблицу будет добавлен новый столбец, определенный в соответствии с конструкцией <определение_столбца>, так же, как при создании таблицы. Например, чтобы добавить столбец с именем Memo в таблицу Request, необходимо выполнить запрос: ALTER TABLE Request ADD Memo VARCHAR(100); При добавлении в таблицу с данными нового столбца, не допускающего значений NULL, необходимо также установить значение по умолчанию с помощью секции DEFAULT, например: ALTER TABLE PaySumma ADD New INT DEFAULT 0 NOT NULL; Использование конструкции ADD <ограничение_таблицы> приводит к добавлению в определение таблицы ограничения, определяемого конструкцией <ограничение_таблицы>, так же, как при создании таблицы. Чтобы для таблицы Request определить ограничения внешних ключей, можно выполнить следующие запросы: ALTER TABLE Request ADD FOREIGN KEY (AccountCD) REFERENCES Abonent(AccountCD); ALTER TABLE Request ADD FOREIGN KEY (ExecutorCD) REFERENCES Executor(ExecutorCD); ALTER TABLE Request ADD FOREIGN KEY (FailureCD) REFERENCES Disrepair(FailureCD); Однако можно добавить эти внешние ключи для таблицы Request и с помощью одного запроса: ALTER TABLE Request ADD FOREIGN KEY (AccountCD) REFERENCES Abonent(AccountCD), ADD FOREIGN KEY (ExecutorCD) REFERENCES Executor (ExecutorCD), ADD FOREIGN KEY (FailureCD) REFERENCES Disrepair (FailureCD); Конструкция ALTER [COLUMN] столбец TO новое_имя_столбца используется для переименования столбца. Например, можно изменить имя столбца Phone в таблице Abonent: ALTER TABLE Abonent ALTER Phone TO HomePhone; Однако попытка переименовать столбец может не удаться из-за проблемы существования зависимостей (на столбец существуют ссылки из ограничений, или он используется в представлениях, триггерах или хранимых процедурах). Конструкция ALTER столбец TYPE {<тип данных> | имя_домена} используется для изменения типа данных столбца. Однако существуют ограничения при изменении типа данных. Так, СУБД не позволит произвести такое изменение типа данных, в результате которого могут потеряться данные. Новое определение столбца должно позволять использовать существующие данные. Например, новое количество символов в столбце не может быть меньше наибольшего размера столбца (количество 21 символов для строковых типов, общее количество разрядов и число разрядов после запятой для десятичных типов). Если попытаться переопределить тип столбца Phone таблицы Abonent с меньшим количеством символов: ALTER TABLE Abonent ALTER Phone TYPE VARCHAR(10);, то будет выдано сообщение «New size specified for column PHONE must be at least 15 characters» (новый размер, определяемый для столбца PHONE, должен быть не менее 15 символов). В СУБД Firebird преобразование числового типа данных в строковый тип требует минимального размера строкового типа (табл. 4.1). Таблица 4.1. Минимальное количество символов для числовых преобразований Тип данных Минимальная длина символьного типа BIGINT 19 (или 20 для чисел со знаком) DECIMAL 20 DOUBLE 22 FLOAT 13 INTEGER 10 (11 для чисел со знаком) NUMERIC 20 (или 21 для чисел со знаком) SMALLINT 6 И, наконец, преобразование символьных данных в другие типы недопустимо. Конструкция Firebird ALTER столбец POSITION номер_позиции используется для изменения порядкового номера столбца в таблице. Конструкция ALTER столбец SET DEFAULT <значение> используется для задания значения по умолчанию для столбца или для изменения значения по умолчанию, назначенного для столбца таблицы при ее создании (в том числе для изменения значения по умолчанию, взятого из домена, на котором определен данный столбец). Изменение значения столбца по умолчанию не оказывает влияния на состояние существующих строк таблицы (даже если в некоторых из них хранится предыдущее значение столбца по умолчанию). Конструкция ALTER столбец DROP DEFAULT используется для удаления значения по умолчанию, определенного для столбца таблицы. Если такое значение не было определено, то выдается соответствующее сообщение. Если столбец определен на домене (значение по умолчанию), то после удаления значения по умолчанию, определенного для столбца таблицы, начинает действовать значение по умолчанию домена. Рассмотрим на примере изменение и удаление значений по умолчанию для столбцов. Допустим, что в созданную ранее (см. разд. 4.2.1) таблицу Days 22 добавлена строка, где Number = 1, а остальные значения берутся по умолчанию. В результате таблица будет иметь вид, представленный на рис. 4.1. Number Dat Event Usr Tel 1 01.09.2013 NULL SYSDBA 111111 Рис. 4.1. Таблица Days до изменения значений по умолчанию Например, с помощью следующего запроса удалим в таблице Days значение по умолчанию столбца Dat, изменим значение по умолчанию столбца Usr на Petrov, а значение по умолчанию столбца Tel, взятое из домена Telephone, изменим с '111111' на '999999': ALTER TABLE Days ALTER Dat DROP DEFAULT, ALTER Usr SET DEFAULT 'Petrov', ALTER Tel SET DEFAULT '999999'; После добавления второй строки с Number = 2 и со значениями по умолчанию таблица Days примет вид, представленный на рис. 4.2. Number Dat Event Usr Tel 1 01.09.201 NULL SYSDBA 111111 3 2 NULL NULL Petrov 999999 Рис. 4.2. Таблица Days после изменения и вставки Конструкция Firebird ALTER столбец COMPUTED BY (выражение); используется для изменения выражения вычисляемого столбца таблицы (но не для изменения типа столбца вычисляемый/невычисляемый), например CREATE TABLE Test (N INTEGER, Dn COMPUTED BY (N * 2)); COMMIT; ALTER TABLE Test ALTER Dn COMPUTED BY (N * 3); Конструкции ALTER столбец SET NOT NULL; и ALTER столбец DROP NOT NULL; используются для изменения признака допустимости значений NULL для столбца. При этом следует учесть, что если столбец основан на домене с ограничением NOT NULL, то ограничение домена перекроет удаление ограничения для столбца. Конструкция DROP столбец используется для удаления существующего столбца таблицы. Удаление столбца запросом ALTER TABLE может завершиться неудачей в случаях:  удаляемый столбец является частью ограничений UNIQUE, PRIMARY KEY или FOREIGN KEY;  удаляемый столбец используется в секции CHECK; 23 удаляемый столбец является частью выражения, например, при создании таблицы в конструкции COMPUTED [BY] (<выражение>);  на удаляемый столбец ссылается другой объект БД, например представление. Удаление ограничения таблицы производится при использовании секции DROP CONSTRAINT запроса ALTER TABLE с указанием имени ограничения. Если при создании таблицы (CREATE TABLE) или изменении ее определения (ALTER TABLE) не задавалось имя ограничения, то удаление такого ограничения становится невозможным. Например, чтобы удалить ограничение, наложенное на таблицу Abonent и имеющее имя Abonent_Not_As_Executor, необходимо воспользоваться запросом: ALTER TABLE Abonent DROP CONSTRAINT Abonent_Not_As_Executor; Для добавления в определение таблицы Abonent ограничения с именем Regular_Expression на структуру номера мобильного телефона абонента можно использовать запрос: ALTER TABLE Abonent ADD CONSTRAINT Regular_Expression CHECK (Phone SIMILAR TO '\([1-9][0-9]{2}\)[0-9]{3}\-[0-9]{4}' ESCAPE '\'). После выполнения данного запроса номер телефона абонента должен вводиться в формате (XXX) XXX-XXXX. Добавить в таблицу Street ограничение Upp, которое запрашивает наименование улицы в верхнем регистре, можно так: ALTER TABLE Street ADD CONSTRAINT Upp CHECK (StreetNM=UPPER (StreetNM)); СУБД Oracle поддерживает все изменения, указанные выше для Firebird, кроме изменения порядкового номера столбца в таблице и изменения выражения вычисляемого столбца таблицы. При этом Oracle позволяет:  переименовывать ограничение таблицы;  включать, отключать ограничение таблицы;  переименовывать таблицу. Запрос ALTER TABLE в Oracle имеет формат: ALTER TABLE базовая_таблица <действие>;,  позволяя, таким образом, выполнять только один вид действия над таблицей.  Действие задается в формате: {ADD <определение_столбца> | ADD <ограничение_таблицы> [DISABLE] | RENAME COLUMN столбец TO новое_имя_столбца | MODIFY столбец <изменение> | RENAME CONSTRAINT <имя_ограничения1> TO <имя_ограничения2> | MODIFY CONSTRAINT <имя_ограничения> NOT NULL | { ENABLE | DISABLE } CONSTRAINT имя_ограничения | DROP { COLUMN столбец | (столбец1, столбец2,…) } | SET UNUSED { COLUMN столбец | (столбец1, столбец2,…) }  24 | DROP CONSTRAINT имя_ограничения | RENAME имя_таблицы TO новое_имя_таблицы}, где <изменение>::= {<тип данных> | DEFAULT { <значение> | NULL }. При использовании в Oracle конструкции ADD <ограничение_таблицы> следует помнить, что ограничение активизируется автоматически в момент создания. Если необходимо создать ограничение, но не активизировать его сразу, следует использовать запрос с опцией DISABLE ALTER TABLE таблица ADD CONSTRAINT имя_ограничения DISABLE, который налагает условие и тут же его отключает. Отключенным называется ограничение, получившее имя, но не введенное в действие. Отключить существующее ограничение можно и с помощью запроса ALTER TABLE таблица DISABLE CONSTRAINT имя_ограничения. Позже любое ограничение можно включить, выполнив запрос ALTER TABLE таблица ENABLE CONSTRAINT имя_ограничения. Удаление значения по умолчанию в Oracle выполняется путем установки значения по умолчанию в NULL: MODIFY столбец DEFAULT NULL. Конструкция DROP COLUMN столбец | (столбец1, столбец2,…) используется для удаления существующего столбца/столбцов таблицы. При этом, вместо того чтобы удалять нежелательные, неиспользуемые или ненужные столбцы, можно пометить их как неиспользуемые. В чем разница между удалением и присвоением статуса неиспользуемого? Неиспользуемый столбец просто не виден пользователям Oracle. По сути, это столбцыневидимки. Их альтернативой является физическое удаление столбцов, когда отбрасываются все содержащиеся в них данные. Если удалять большие таблицы, то это может существенно сказаться на производительности, поскольку удаление данных и перезапись строк таблицы может потребовать значительного времени. Это не очень удобно в середине рабочего дня, когда с БД работает множество пользователей. Присвоение столбцу статуса неиспользуемого требует гораздо меньше времени. Пометить столбец как неиспользуемый гораздо быстрее, кроме того, при этом существует возможность отмены данного действия, тогда как команда удаления необратима. Присвоение столбцу/столбцам статуса неиспользуемых осуществляется с помощью запросов вида ALTER TABLE таблица SET UNUSED COLUMN столбец; ALTER TABLE таблица SET UNUSED (столбец1, столбец2,…); Изменить имена ограничений в Oracle можно с помощью запроса в формате ALTER TABLE таблица RENAME CONSTRAINT столбец1 TO столбец2; 25 Oracle в запросе ALTER TABLE позволяет переименовывать саму таблицу. Для переименования таблиц и других объектов БД (например, представлений и синонимов) применяется конструкция RENAME. В этом случае Oracle переименует таблицу и автоматически наложит на нее все условия целостности, добавит индексы и связи с другими объектами, которые были соотнесены со старым именем таблицы. Тем не менее, некоторые объекты, ссылавшиеся на старую таблицу, например записанные процедуры, функции и представления, больше работать не будут. Переименовывая таблицу, нужно быть предельно внимательным, поскольку это может иметь нежелательные последствия для БД. Запрос переименования имеет следующий синтаксис: ALTER TABLE RENAME имя_таблицы TO новое_имя_таблицы; СУБД MS SQL Server, как и Oracle, поддерживает изменение имени таблицы, однако позволяет выполнять данную операцию только в Management Studio. В запросе ALTER TABLE изменить имя таблицы нельзя. Изменить имя столбца также возможно только в Management Studio. Однако такое изменение невозможно, если столбец присутствует в ограничении CHECK, в выражении для получения значения вычисляемого столбца. Существенных отличительных особенностей базовый синтаксис запроса ALTER TABLE в MS SQL Server не имеет, подробнее можно ознакомиться в [23]. 26